topic Re: Rangesum question in App Development

Rangesum question

marcoserilio — Tue, 23 Jan 2018 11:10:55 GMT

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

Re: Rangesum question

hic — Tue, 23 Jan 2018 11:38:22 GMT

Use the total qualifier:

RangeSum( total <expression> )

Above( total <expression> )

HIC

Re: Rangesum question

sunny_talwar — Tue, 23 Jan 2018 12:15:16 GMT

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

Re: Rangesum question

marcoserilio — Tue, 23 Jan 2018 13:07:52 GMT

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) ))

Re: Rangesum question

marcoserilio — Tue, 23 Jan 2018 13:11:14 GMT

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

Re: Rangesum question

sunny_talwar — Tue, 23 Jan 2018 13:17:52 GMT

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.

Re: Rangesum question

hic — Tue, 23 Jan 2018 14:36:43 GMT

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

Re: Rangesum question

hic — Tue, 23 Jan 2018 15:02:01 GMT

Try

If(

Year=Above(total Year),

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

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

)

Re: Rangesum question

sunny_talwar — Wed, 24 Jan 2018 03:11:22 GMT

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

Best,

Sunny

Re: Rangesum question

marcoserilio — Wed, 24 Jan 2018 09:04:38 GMT

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

Re: Rangesum question

hic — Wed, 24 Jan 2018 09:08:36 GMT

OK, then it's just a matter of moving the condition to the dimension above Year. Try

If( ColorCode=Above(total ColorCode), ...

instead

HIC

Re: Rangesum question

marcoserilio — Wed, 24 Jan 2018 09:13:37 GMT

I'll give it a try right away

Re: Rangesum question

Thiago_Justen_ — Wed, 24 Jan 2018 09:22:33 GMT

Fantastic HIC. Thanks for sharing this.

I'm learning a lot reading your solutions.

Cheers

Re: Rangesum question

marcoserilio — Wed, 24 Jan 2018 10:38:18 GMT

Hi Eric, close but not yet. Now the first row of the new dimension is correct, but from the 2nd row on, still the rolling is only taking into consideration current dimension figures:

see:

the February testmsr value should be 89 instead: 49 testmsr value from January + 40 Po units in Febrary-0 Shd in February - 0 WhStockQty in February. I have tried to adapt your script but I didn't make it

Re: Rangesum question

hic — Wed, 24 Jan 2018 11:02:28 GMT

I assume that the column TestMsr is the one that we're looking at. But since you changed the name of the column, you need to change this in the expression to reflect that.

The expression

Rangesum(Above( total [Po-Consolidated] ) , X )

will take the value of Po-Consolidated from the line above, and add X. But now you instead want the value of TestMsr from the line above.

So, then you need to change the expression to

If(

ColorCode=Above(total ColorCode),

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

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

)

HIC

Re: Rangesum question

marcoserilio — Wed, 24 Jan 2018 11:10:40 GMT

My bad. Thanks for the tip and the explanation as well, you've been a great help