8 Replies Latest reply: Jun 24, 2017 9:22 AM by Abid Peerzade

Periodic Calculation

Hi All,

I have Date and Category as dimensions and a measure and the data looks something like this

 Date Category Value A 1/1/2012 10 A 5/1/2013 20 A 3/1/2014 30 A 4/1/2014 10 B 1/1/2013 20 B 6/1/2014 30 B 9/1/20114 15 B 12/1/2014 40

My objective is to calculate the previous year's value from the latest date within each category, expecting the output to be like

 Date Category Value A 5/1/2013 20 B 1/1/2013 20

I am trying the following set code

but the highlighted expression is evaluating to max of entire date column. Can we modify this code to achieve the objective or is there any other method we can adapt. Please guide me.

Many Thanks,

Abid

• Re: Periodic Calculation

May be create a new field in the script like this

Year(Date) as Year

and then try this:

FirstSortedValue({<Year = {"\$(=Max(Year) - 1)"}>}Aggr(Sum(Value), Date), -Date)

FirstSortedValue({<Year = {"\$(=Max(Year) - 1)"}>} Aggr(Sum(Value), Date, Category), -Aggr(Date, Date, Category))

• Re: Periodic Calculation

Hi Sunny,

Thanks a bunch for your quick response, this works for me. One more request. How do we handle the tie case when I use the FisrtSortedValue() function in this scenario? because I am getting null values for tie

• Re: Periodic Calculation

FirstSortedValue(Distinct .....)

• Re: Periodic Calculation

I am Sorry, this trick also yielding the same results as the earlier one, because "\$(=Max(Year) - 1)" is evaluating to the max of entire Year Field (2014 that is indeed the max year for both the categories). This isn't working when the Year of max date for the categories is different. please help.

• Re: Periodic Calculation

Not sure I understand... do you have a sample to show your issue?

• Re: Periodic Calculation

Please try with this data values

 Date Category Value A 1/1/2012 10 A 5/1/2013 20 A 3/1/2014 30 A 4/1/2014 10 B 1/1/2014 20 B 6/1/2014 30 B 9/1/2015 15 B 12/1/2015 40

Expecting the results to be

 Date Category Value A 5/1/2013 20 B 6/1/2014 30
• Re: Periodic Calculation

May be this then,

FirstSortedValue(Aggr(If(Year = (Max(TOTAL <Category> Year) - 1), Sum(Value)), Date, Category), -Aggr(If(Year = (Max(TOTAL <Category> Year) - 1), Date), Date, Category))

• Re: Periodic Calculation

Thanks again Sunny, it worked perfectly