Date formatting and variable in SET expression, Moving Annual Total

[JohanLind1979]

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?

[stevedark · Accepted solution]

Hi @JohanLind1979 

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

[JohanLind1979]

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.

[stevedark]

Excellent, glad it works for you.