Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Qlik friends, I have a problem creating a SET expression for Moving Annual Total. I´ve got fields Period, Amount, Year, Month:
Format of field Period is:
202009
202008
202007
...
and I also got a variable with the start date of max(Month). The variable is called $(vMaxSelectedMonth_StartDate) and if you select Month = September, the result of this variable is '2020-09-01'.
Now I need the Moving Annual Total of Amount. This is the sum of Amount for Periods 201910 - 202009 if you select September 2020. But I also want separate expression for Moving Annual Total previous Month, that are periods 201909 - 202008 if you select September 2020. Separate expression for Month-2, Month-3 and so on until Month-12.
For the selected month this works fine:
Sum({<Period = {">$(=Max(Period)-100)"}>} Amount)
but it doesn´t work for Month-1, Month-2 and so on...
I´m thinking I need to change the format of Period to 'YYYY-MM-DD' and compare to the variable $(vMaxSelectedMonth_StartDate). Can this be done in a SET expression? What would that look like?
With set analysis and dates the formatting is really important. Having the period as a number makes things simpler, for sure.
You can format the content of the variable to match the period field like this:
=Date(Max(Month), 'YYYYMM')
That should then allow you to use it in the set analysis against the period.
If you need to take off a number of months though you have a problem that the numbers are not fully sequential (i.e. 202001 does not follow 202012). You can get around this by creating a field which is months since 1900, by doing this in your load script:
(Year(Date)*12)+Month(Date)) as MonthNo,
This gives you a numeric sequence of months, so to get the number of the month three before the latest you can do max(MonthNo)-3. This can simplify set analysis and prior period comparisons, but is not useful to show the user in any way shape or form.
Hope that helps.
Steve
With set analysis and dates the formatting is really important. Having the period as a number makes things simpler, for sure.
You can format the content of the variable to match the period field like this:
=Date(Max(Month), 'YYYYMM')
That should then allow you to use it in the set analysis against the period.
If you need to take off a number of months though you have a problem that the numbers are not fully sequential (i.e. 202001 does not follow 202012). You can get around this by creating a field which is months since 1900, by doing this in your load script:
(Year(Date)*12)+Month(Date)) as MonthNo,
This gives you a numeric sequence of months, so to get the number of the month three before the latest you can do max(MonthNo)-3. This can simplify set analysis and prior period comparisons, but is not useful to show the user in any way shape or form.
Hope that helps.
Steve
That was really really smart! Love that solution, thank´s! One slight adjustment needed though, I think it should be
(Year(Date)*12)+(Month(Date)) as MonthNo
Parenthesis missing in your post....
Also I don´t think I need your formula =Date(Max(Month), 'YYYYMM') for the variable. Max(Period) is the same result in my case.
Excellent, glad it works for you.