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.

[Deleted User]

First of all you're not stating of you're doing this in the RPD or in an analysis.

Secondly you're not stating what's going wrong. "does not work correctly" isn't necessarily a precise way to describe a technical problem upon which we can base our inputs especially since you claim that other AGO examples are working fine.

How To Ask Questions The Smart Way

cesar.advincula.o

I could try to guest that you are using the "Customer Dimension", and in your customer dimension you have "number available", "customer id" coluimns

and you want something like this

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

Kind Regards,

2973433

In RPD, but I tried in analysis too and didn't work as well.

And actually I said what is the problem. Problem is that Measure with Ago function (Number available) shows the same values as Measure without Ago function (Number available previous period).

What else can I say?

@cesar.advincula.o yes exactly

cesar.advincula.o

Hello,

I think you have a model like this:

-Dimension Time

-Dimension Customer

-Fact of your Dimensions

When you use the AGO/ROLLING PERIOD/TO_DATE, please you should to use in a FACT TABLE, you shouldnt use that in MEASURES BASED IN CALCULATIONS OF DIMENSION , thats what you get the SAME VALUE, I recommend to do an ETL, to get "cast (count (distinct  case  when "XXX"."Customers"."available" > 0 then  "XXX"."Customers"."ID" else null end) as double)," after it your AGO function works corrrect. Another think, if your dimension time.

Kind Regards,

Thomas Dodds

Your problem desciption is missing any and all references to a SQL TSI_### interval ... BI server knows it needs to go back 1 step, but on WHICH LEVEL?

2973433

Sorry Cesar, I was unclear.

I'm using this measure on fact, not dimension. So it is not a problem.

@Thomas Dodds I don't think it is needed. In that case OBI is displaying values based on what Time attribute you use. For example if I use put on analysis Month it should show me values from previous month. If year - it should show values from previous year and so on.

Anyway I tried also with defined TimeLevel and didn't work too.

cesar.advincula.o

Hello,

Please, could you tell me the RPD model that you have.

Tell me about the columns of your dimensions and the fact that you use, please, if you tell me that i could replicate the requirement for you and get the real problem that you have.

Thank you.

Kind Regards,

[Deleted User]

SQL_TSI isn't for time series calculations but I'd definitely suggest to the OP that he should first of all try an explicit mentioning of the hierarchy level he wants to use to go back instead of omitting it:

AGO("Base Facts"."1- Revenue", "Time"."Time Hierarchy"."Year", 1)

2973433

Christian, but my requirement is to present it for every level, not for only one specified.

Anyway as I said, I tried also with defined level and problem was still the same.

@cesar.advincula.o It is quite simple:

I have fact Customers and dimension Time. They are joined i BMM and in physical layer.

Everything else stay as in the first post.

Maybe the problem is that there too many functions in the measure (cast, case when, count distinct) ?

For other measures which work correctly I use only count or sum.

cesar.advincula.o

Hello,

For your last question, it is not any problem, the problem is when you create a measure (count for a column of dimension, etc).

Please , check

Kind Regards,