Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
vikas12345
Contributor II
Contributor II

Column wise rolling sum of n period in pivot table

In pivot table, I have one dimension in row and in column one dimension is monthname. I want to calculate rolling sum of sale column wise and I have two years of data. so there will be 24 columns. I am using expression for rolling sum as below,

Rangesum(before(Sum(Sales),0,ColumnNo(total)))

But my task is show current financial year data(From April to current month) in pivot table. How can I achieve this?

@sunny_talwar @marcus_sommer @kaushiknsolanki @rwunderlich @Digvijay_Singh  @Anil_Babu_Samineni @Michael_Tarallo 

Labels (5)
4 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Maybe something like this:

Rangesum(before(Sum({< FinancialYear = {2021}>} Sales),0,ColumnNo(total)))

Of course you need to have this field within the data-model. You may reach this also with other conditions on month or similar but linking the normal calendar with a financial calendar will simplify many tasks.

- Marcus

vikas12345
Contributor II
Contributor II
Author

But by using this, rolling sum will be start from April 2021 and previous values will not include in rolling sum.

marcus_sommer
MVP & Luminary
MVP & Luminary

Yes, interrecord-functions will only use those cells which are available. Thinkable is to extend the logic with an aggr() to include within the inner aggregation a wider period and restricting it within outer aggregation to a smaller area. But it may not very trivial to implement such a logic.

Another approach could be to display just a few more periods as for the real purpose needed and you may also add another dimension to the object so that you have there the financial year, too.

- Marcus

vikas12345
Contributor II
Contributor II
Author