Set Analysis

[fcecconi]

I am not sure if this can be accomplished, but I need to create a set of analysis for not the current year.

I am using =avg({ < Discharge__PREV_YTD ={1}>}Value)

However, the data is relatively new and we don't have data in the table for Jan and Feb yet, so there really isn't any data yet for a previous YTD.

Any way around this?

[sergio0592]

You want average for the previous year of the selected year, that's it?

If it's the case, try : avg({$<Discharge__PREV_YTD={$(=Discharge__PREV_YTD-1)}>} Value)

[Anil_Babu_Samineni]

Data consumes as per your data

Even, If you don;t have Jan and Feb data if you are selecting December which data you want to show. You meant it is you need to show as November data as you think. This is just assuption

Avg({<Month = {'$(=Max(Month)-1)'}>}Value)

[fcecconi]

I want to use the Discharge_PREV_YTD flag.  This is updated on every load.  Since the earliest data is Mar 2016, Jan and Feb 2016 are missing.  I would like to create an expression that handle this until data is loaded and not hard code anything.  I am also anticipating that the users would like to do month vs month comparison, so I need to figure this out.

[Anil_Babu_Samineni]

Can you elaborate the Flag Discharge__PREV_YTD ??

[fcecconi]

It is just a value of 0 and 1 to indicate if the date should be included.  For example 2/22/2016 should be flagged as 1 to show that it is a date in the previous YTD range.

[sergio0592 · Accepted solution]

I managed this case with a flag defined in the ETL. And for all my expressions, i have :

-if user select current month (not over), i use the flag for compare same period for previous year.

-if user select a past month (over), i don't use flag.

I have two flags, one for month and one for year (cumulate from 01/01).

In the attached file, you have what the two flags make on my Temporal Table (at 20/02/2017).

Is it that you're trying ton achieve??

[fcecconi]

Jean

We were also setting flags.  It appears to have been an issue with the variable we were using.  If we take the code and enter it into the object, it works fine.