Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
marcoserilio
Contributor III
Contributor III

Rangesum question

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:

Pivot.JPG

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

Capture.JPG

15 Replies
hic
Former Employee
Former Employee

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

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

instead

HIC

marcoserilio
Contributor III
Contributor III
Author

I'll give it a try right away

Thiago_Justen_

Fantastic HIC. Thanks for sharing this.

I'm learning a lot reading your solutions.

Cheers

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
marcoserilio
Contributor III
Contributor III
Author

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

Capture.JPG

hic
Former Employee
Former Employee

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

marcoserilio
Contributor III
Contributor III
Author

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