Get sum for calendar year up to previous month

[andreTc]

Hi everyone,

I am trying to compare the sum of page views between the current and preview calendar years up to last month and same month last year respectively. E.g 2023 = 01 Jan - last month (July) and 2022 : 01 Jan - 31 July 2022.

I have this expression for current year: 

Sum({< CurrentOrPreviousYearID={1}, 

        ReportingMonthNum = {"<=$(=Num(Month(Today())))"}>}

            WebPageviews)

and this one for previous year:

Sum({<CurrentOrPreviousYearID={2},

ReportingMonthNum = {"<=$(=Num(Month(Today()))-12)"}>}

           WebPageviews)

The firm one seems to work but the second does not.

Can you please confirm the first is correct and tell where I have it wrong on the second?

Many thanks

[Oleg_Troyansky · Accepted solution]

Hi Andre,

It's hard to confirm the validity of the first expression without knowing how your data is organized and how your months are numbered.

I can tell you what appears to be wrong with the second expression though. The function num(Month(Today())) returns a number from 1 to 12. When you subtract 12 from it, you will likely get a negative number, which probably does not match any of your existing values of ReportingMonthNum. So, to fix this formula, look into it.

Since you are already using something similar to a flag, why don't you create conditional flags in your Calendar table, and simplify your Set Analysis this way. Calculate any logical conditions (current YTD, prior YTD, etc.) in your calendar and assign values 1 or 0, based on the condition. Then you will simply test these flags for 1 in your Set Analysis.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

[Vegar]

If your first expression is working correctly then you should be able to do this for the second one:

Sum({< CurrentOrPreviousYearID={2}, 

        ReportingMonthNum = {"<=$(=Num(Month(Today())))"}>}

            WebPageviews)

(You can do this because Month will have the same value today as it did 12 months ago.)