Rolling prior month comparison

[HeyturnontheAC]

Hi All,

    I have data stored at the fiscal day level, with a fiscal month field included. I'm trying to create a 4 column table to display fiscal month, product category, sum of qty, and sum of qty of the prior month.

So far i've only found answers on how to create a current-previous month metric, but that involves using a set analysis that results in a constant evaluation on the right side of the expression (ie. MONTH_NO = {$(=MAX(MONTH_NO)-1)}, when evaluated is: MONTH_NO = 11)

What I'm trying to do is create a metric where the right side of the set analysis is also a field, something along the lines of (MONTH_NO = MONTH_NO -1).

So in my table, For the row with month 12, i would display sum of qty for month 12 within the category, and then sum of qty for month 11 within the category.

For the row with Month 11, i would display sum of qty for month 11 within the category, and then sum of qty for month 10 within the category.

Is this rolling prior month calculation possible with set analysis?

Thanks!

[Anil_Babu_Samineni]

Check once Above() function in Qlik

[HeyturnontheAC]

So after looking into Above() I'm finding that the calculation is evaluated at the level of the lowest sorted dimension, so in my example, in order to calculate each product categories qty of the previous month, i would have to first sort by category, and then by month. However in the table that i am attempting to create, the desired display would be to sort by month first, and then category, like so:

Month     Category           Sum of Qty           Sum of Previous Month Qty
1                AA                           122
1                BB                           171
2                AA                            623                            122
2                BB                           423                             171
3                AA                           241                             623
3                BB                           113                             423

In addition, once i select a given month, this breaks the Above() function, as it no longer has a month above to calculate. I have other graphs within the app where the user may select a month, so even if a particular month is selected, i would still like this graph to be able to calculate "vs previous month".

[simonB2020]

Did you ever find a solution to this ?

Is exactly what I am struggling with right now 😥

[HeyturnontheAC]

No 😞 

I think my original post would be the solution we're looking for; where we can create a set analysis expression with a field name, rather than with a hardcoded value. Perhaps there is a way to represent this with a different syntax, but as far as I know, it's not possible. It would be exceptionally helpful as I would like to revise my method of calculating both previous month AND previous year metrics....

What methods have you tried? Maybe there's an intersect with my attempts and your attempts that can work.

[simonB2020]

I had another go over the weekend and resorted to provisioning this via the load script.

Basically for each record, calculating & appending values for previous month, quarter, year etc ...

Am also thinking of pre-calculating the deltas in similar fashion,  in the hope of making the final app more performant at runtime ?

More faff in the load, but I have found makes debugging a lot more transparent as you can see values with your own eyes at any level instead of relying upon messy set analysis formulas and crossing fingers.

Original Data
Country	Region	Date	Sales
UK	North	20220-01-01	100
UK	South	20220-01-01	10
UK	North	20220-02-01	1000
UK	South	20220-02-01	100
After Load Script Transformation
Country	Region	Date	Sales	prev_month
UK	North	20220-01-01	100
UK	South	20220-01-01	10
UK	North	20220-02-01	1000	100
UK	South	20220-02-01	100	10

[HeyturnontheAC]

Yeah this is essentially what I've been doing. The frustrating part is that it requires a pre-aggregation based on granularity that needs to be defined in the data load, rather than adjustable on the front end. If i want to make a chart showing month to LY, and a separate graph showing day level information, it couldn't be done.

[simonB2020]

I added 4 additional columns for previous [day; month; quarter; year]

Agree it could get ridiculous ....