Rangesum question

[marcoserilio]

Hi Guys,

I've got a rangesum function in a pivot table which I can't make it work as I need.

This is the formula I'm using, it works fine till i keep CalendarYear dimension out of the pivot table, once I put it inside the pivot, the  rolling is interrupted at any new calendar year:

The January yellow highlighted value should be 47 instead , just as in the following screen shot, with no calendar year

[hic]

Use the total qualifier:

RangeSum( total <expression> )

Above( total <expression> )

HIC

[sunny_talwar]

Hi Henric -

I have never seen this syntax before. I have seen TOTAL within Above() function, but never seen RangeSum() with TOTAL. Also, TOTAL within Above() doesn't really allow to use field names between <>. But it seems that RangeSum() does. Do you have a blog where we can read more about this?

Thanks,

Sunny

[marcoserilio]

I forgot to paste the formula I'm using:

rangesum( above( sum(PurchasePlanQuantity),0,RowNo()+12) )-(rangesum( above( sum(ShippedQty),0,RowNo()+12) )+rangesum( above( sum(WhStockQty),0,RowNo()+12) ))

[marcoserilio]

Hi Henric, thanks for your answer, I tried and put a total in the editor, but qlik doesn't allow the total predicate

[sunny_talwar]

This is what confuses me... I have never seen TOTAL within RangeSum, but if Henric has mentioned about this, I would think that we are probably missing something.

[hic]

Sunny

My bad. Temporary brain drop... The total qualifier should of course be in the Above() function.

Also, it won't work with field specifiers after the total qualifier. So the work around must be a conditional evaluation like

If(Year=Above(total Year),Above(total Sum(Sales)),0)

HIC

[hic]

Try

If(

Year=Above(total Year),

Rangesum(Above( total [Po-Consolidated] ) , Sum(PurchasePlanQuantity), -Sum(ShippedQty), -Sum(WhStockQty) ),

RangeSum( Sum(PurchasePlanQuantity), -Sum(ShippedQty), -Sum(WhStockQty))

)

[sunny_talwar]

Never thought of using this work around, this is amazing. Thanks for sharing this.

Best,

Sunny

[marcoserilio]

Hi Enric, awesome script but it's still giving me what I've got already. The range sum of the Calendar Year dimension, I'm trying on the other hand to include the above calendar year dimension into the current calendar year dimension.

The 29 units to which the arrow is pointing should be 49 instead