Rangesum or ?

[ulyxess1983]

Hi folks,

Please, give an idea how to solve this:

I need chart (table pivot or straight) with 3 dimensions - Date, RefId, Customer and two measures - Ordered and Delta

ordered is just a sum of ordered amount on a date for an order of customer

delta should take current stock minus ordered plus receipt or transfer. delta should be cummulated through all the table, so I'll be able to see when we'll be out of stock.

using this:

sum(StockPcs)+sum({$<Status = {'Sales Orders'}>} QtyPcs)+sum({$<Status = {'Receipt', 'Transfer'}>} QtyPcs)+rangesum(below((sum({$<Status = {'Sales Orders'}>} QtyPcs)+sum({$<Status = {'Receipt', 'Transfer'}>} QtyPcs)), -1, 2000)))

yes, i could use "above" instead of "below"

and if I use 1 dimension only - everything works fine. so, the only question is - how to make it with 3 dimensions?

example sheet attached.

thanks in advance.