Sum in Qlik Sense pivot table

[ramkrishna86]

Hi,

I need your help regarding below functionality. I have data in pivot table as below-

What i want is Begining of next month should be ending of previous month as below-

How can i do this, i have attached QVF file.

Thanks for your help!

[sunny_talwar]

May be try this

Beginning

RangeSum(RangeSum(Before(Sum(B)+Sum(C)-Sum(D), 1, ColumnNo())), First(Sum(A)))

Ending

Column(1) + Column(2) + Column(3) - Column(4)

[ramkrishna86]

Hi Sunny,

This only works for first two date in pivot table, I want this functionality for complete pivot table.

Note: Range Sum function not works in pivot table as works in other chart.

Thanks!

[sunny_talwar]

Really? Looks like the numbers are matching for me

App Screenshot

vs. your screenshot

Where is the difference?

[ramkrishna86]

Hi Sunny,

I tried the same but can't get result, could you please suggest where i am making mistake.

I have attached QVF file.

Thanks!

[sunny_talwar · Accepted solution]

I think you don't want to use Sum(A) in the before function

RangeSum(RangeSum(Before(Sum(A)-Sum(B)-Sum(C)+Sum(D)+Sum(E)+Sum(F)-Sum(G)+Sum(H), 1,

ColumnNo())), First(Sum(A)))

Try this:

RangeSum(RangeSum(Before(-Sum(B)-Sum(C)+Sum(D)+Sum(E)+Sum(F)-Sum(G)+Sum(H), 1,

ColumnNo())), First(Sum(A)))

[ramkrishna86]

Thank you So much Sunny

[P_Kumar]

Hi Sunny,

I have observed following in your test app.  The calculations where ever Rangesum is being used, isn't showing correctly upon filter selection where as it's showing correct without a filter.  Kindly check the screenshots and advise on the issue since I am facing same issue with one of my apps.  Thanks