Categories
- All Categories
- 127 Oracle Analytics News
- 23 Oracle Analytics Videos
- 14.5K Oracle Analytics Forums
- 5.5K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 47 Oracle Analytics Trainings
- 7 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 8 Oracle Analytics Industry
- Find Partners
- For Partners
Function Ago does not work
Answers
-
Thank you for your effort,
I need to check it deeply.
I will let you know on Monday.
0 -
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!
0 -
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 ..
0 -
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).
0 -
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.
0 -
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,
0 -
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 ....
0 -
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.
It's just standard model. This fact has relationship to dim_time in physical model and BMM as well and that's it.
0 -
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)
0 -
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,
0
