Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

abid_rudder
New Contributor

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

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

8 Replies
MVP
MVP

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

Capture.PNG

abid_rudder
New Contributor

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

MVP
MVP

Re: Periodic Calculation

May be add a distinct?

FirstSortedValue(Distinct .....)

abid_rudder
New Contributor

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.

MVP
MVP

Re: Periodic Calculation

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

abid_rudder
New Contributor

Re: Periodic Calculation

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

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

abid_rudder
New Contributor

Re: Periodic Calculation

Thanks again Sunny, it worked perfectly