Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
OK, then it's just a matter of moving the condition to the dimension above Year. Try
If( ColorCode=Above(total ColorCode), ...
instead
HIC
I'll give it a try right away
Fantastic HIC. Thanks for sharing this.
I'm learning a lot reading your solutions.
Cheers
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
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
My bad. Thanks for the tip and the explanation as well, you've been a great help