nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Function Ago does not work

2973433

Hi guys,

I have one measure named "Number available" with calculation like this:

cast (count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double)

and it works correctly,

then I have measure "Number available previous period":

Ago("XXX"."Customers"."Number available" , 1)

And the second one does not work correctly,

In analysis when I use both measures with Time (Month ie), both show exactly the same value.

Do you have any ideas why it happens?

I just want to add that my Time Dimension seems fine since other measures based on Ago function work correctly.

Find more posts tagged with

Accepted answers

All comments

2973433

Thank you for your effort,

I need to check it deeply.

I will let you know on Monday.

Thomas Dodds

Correct ... my mistake ... wasn't reading it right ... needs the time hierarchy level not SQL_TSI ... @Christian Berg - I ain't perfect! lol ... good catch!

Thomas Dodds

The SampleApp (v506) has the examples you are looking for ... it has dynamic and fixed ... if you don't have the VM, I can fire mine up after work today and post the details (RPD) from these ..

[Deleted User]

No worries I just wanted to be clear on things as there's already many confusions flying around in here.

Anyways I'm with you on the SampleApp track as there's tons of examples in there and it's such a basic functionality that the hickup must lie in a bizarre RPD config or another.

Probably something like derived calc (post-agg) versus physical mapping calc (pre-agg).

2973433

Okay here we go:

on first screen column1 and column2 and my measures which we're discussing. As you can see both show the same value which is obviously wrong.

Additionally I added two other measures (not connected with this topic) that show correct values.

And for the record I'm putting how these measures look in admin tool:

Honestly I don't know what I'm doing wrong.

I decided to do in ETL proper calculated fields and I think it's gonna solve my problem.

cesar.advincula.o

Hello,

You could everything you want in the ETL process, however, i dont know why you got the same problem, I answered you about the model in the RPD, and whats about the results, and I didn't have any problem, if you please give me an screenshoot about oyu BMM and Physical Layer.

Kind Regards,

Thomas Dodds

Don't do the case statement with your ago() ... just base it on the logical column that already evaluates the case ... also since you are using logical columns on logical columns, @Christian Berg's comment of "

Probably something like derived calc (post-agg) versus physical mapping calc (pre-agg)" is starting to take form as to why this isn't working the way you expect it to.

You'vew already started in the direction I would point you - back to the database. I wouldn't precalculate the prior value though (in case you have historical restatements happeing) ... rather my time dimension would have the prior period (LY, LM, YESTERDAY) keys represented on each row. Then you can alias\ your fact multiple times and join one to the base (current date)key and the rest to the historical keys. So now the user picks current period, they also can have on the same row LY, LM, YESTERDAY ....

2973433

Don't do the case statement with your ago()

I guess that's the reason why it is not working, but why? Is it forbidden to do case statem with Ago function? I didn't know that before.

You'vew already started in the direction I would point you - back to the database.

Yes, that's what I am going to do.

@cesar.advincula.o

It's just standard model. This fact has relationship to dim_time in physical model and BMM as well and that's it.

DanPryke

I'd agree with @Thomas Dodds, you should have a column in the RPD that does your CASE statement then have another column where you do the ago and reference the name of that column so instead of

ago(case when x then 1 end)

You'd have

ago("Fact"."Case",1)

A lot cleaner and means that the aggregation of the column is done before it attempts the AGO function.

I know you've said that some of your AGO columns work but it is probably worth the time to look at the basic structure and dimension data behind your hierarchy:

1. Make sure that at each level of your time dimension hierarchy you have a unique number version of the week in the database i.e. 201605 and use this as your chronological key

2. Look at the content level of your fact table logical source against the time dimension, make sure it's pointing at the right level (most likely Day or Detail)

cesar.advincula.o

Hello,

Return to your first post, you say that you have two measures:

"Number available" : cast (count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double)

"Number available previous period": Ago( cast (count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double) , 1)

And also I asked you about the model because the model is the key for your requirement, and you said to me that is a standard model

the "f_Customer_Demo" is your "Fact" and Period is your "Time Dimension",

I mean you have the model like this, its what I understand:

Thats right?

There is no problem if you use AGO Function with a case statement in measure, if your measure if part of the FACT table, the problem its when you got the measure from the DIMENSION table.

Now, lets go to your solution, with the scenary of your requirements:

1. Criteria Tab:

2. Formula by Each column:

3. Database:

4. Structure for all columns:

Remember the logic that you are using:

"Number available" : cast (count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double)

"Number available previous period": Ago( cast (count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double) , 1)

Analyze:

20151001:

Cast Count column which is "(count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double)", return 2.

Cast Count AGO column which is" Ago( cast (count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double) , 1)", return 0.

20151101:

Cast Count column which is "(count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double)", return 2.

Cast Count AGO column which is" Ago( cast (count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double) , 1)", return 2.

20151201:

Cast Count column which is "(count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double)", return 5.

Cast Count AGO column which is" Ago( cast (count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double) , 1)", return 2.

20160101:

Cast Count column which is "(count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double)", return 0.

Cast Count AGO column which is" Ago( cast (count (distinct case when "XXX"."Customers"."available" > 0 then "XXX"."Customers"."ID" else null end) as double) , 1)", return 5.

Note: You could apply this formula for your RPD also, you will get the same results.

Kind Regards,