Periodic Calculation

[Anonymous]

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

sum( {<Year(Date) = {'$(=Year(AddYears(max(Date),-1)))'}>} Value)

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

[sunny_talwar]

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))

[Anonymous]

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

[sunny_talwar]

May be add a distinct?

FirstSortedValue(Distinct .....)

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar · Accepted solution]

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))

[Anonymous]

Thanks again Sunny, it worked perfectly