Announcements
Join us on Sept. 17 to hear how our new GenAI Assistant empowers data professionals: REGISTER
cancel
Showing results for
Did you mean:
Contributor II

## Rolling prior month comparison

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!

Labels (3)

• ### Set Analysis

7 Replies

Check once Above() function in Qlik

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Contributor II
Author

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

Creator

Did you ever find a solution to this ?

Is exactly what I am struggling with right now 😥

Contributor II
Author

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.

Creator

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

Contributor II
Author

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.

Creator