topic Re: Rolling prior month comparison in App Development

Rolling prior month comparison

HeyturnontheAC — Sat, 16 Nov 2024 17:37:39 GMT

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!

Re: Rolling prior month comparison

Anil_Babu_Samineni — Mon, 11 Jan 2021 20:04:14 GMT

Check once Above() function in Qlik

Re: Rolling prior month comparison

HeyturnontheAC — Tue, 12 Jan 2021 14:55:51 GMT

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".

Re: Rolling prior month comparison

simonB2020 — Fri, 22 Apr 2022 10:07:51 GMT

Did you ever find a solution to this ?

Is exactly what I am struggling with right now 😥

Re: Rolling prior month comparison

HeyturnontheAC — Mon, 25 Apr 2022 15:14:01 GMT

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.

Re: Rolling prior month comparison

simonB2020 — Mon, 25 Apr 2022 15:29:56 GMT

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

Re: Rolling prior month comparison

HeyturnontheAC — Mon, 02 May 2022 17:39:41 GMT

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.

Re: Rolling prior month comparison

simonB2020 — Mon, 02 May 2022 21:41:40 GMT

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

Agree it could get ridiculous ....