Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Periodic Calculation

Hi All,

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

  

DateCategoryValue
A1/1/201210
A5/1/201320
A3/1/201430
A4/1/201410
B1/1/201320
B6/1/201430
B9/1/2011415
B12/1/201440

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

  

DateCategoryValue
A5/1/201320
B1/1/201320

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

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

Capture.PNG

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

Please try with this data values

DateCategoryValue
A1/1/201210
A5/1/201320
A3/1/201430
A4/1/201410
B1/1/201420
B6/1/201430
B9/1/201515
B12/1/201540

Expecting the results to be

DateCategoryValue
A5/1/201320
B6/1/201430
sunny_talwar

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
Not applicable
Author

Thanks again Sunny, it worked perfectly