Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all
I am still unable to compute cumulative by customer horizontally, please help urgently.
Source: | ||||||||
Customer | Metrics | Type | 31/01/21 | 28/02/21 | 31/03/21 | 30/04/21 | 31/05/21 | 30/06/21 |
A001 | Sales | Actual | 10,000 | 40,000 | 70,000 | 100,000 | 130,000 | 160,000 |
A001 | Cost | Actual | 7,000 | 9,200 | 84,000 | 83,000 | 93,000 | 112,000 |
A001 | Profit | Actual | 3,000 | 30,800 | (14,000) | 17,000 | 37,000 | 48,000 |
B001 | Sales | Actual | 15,000 | 45,000 | 75,000 | 105,000 | 135,000 | 165,000 |
B001 | Cost | Actual | 10,500 | 9,200 | 84,000 | 83,000 | 93,000 | 115,500 |
B001 | Profit | Actual | 4,500 | 35,800 | (9,000) | 22,000 | 42,000 | 49,500 |
Answer : | ||||||||
Customer | Metrics | Type | 31/01/21 | 28/02/21 | 31/03/21 | 30/04/21 | 31/05/21 | 30/06/21 |
A001 | Sales | Cumulative | 10,000 | 50,000 | 120,000 | 220,000 | 350,000 | 510,000 |
A001 | Cost | Cumulative | 7,000 | 16,200 | 100,200 | 183,200 | 276,200 | 388,200 |
A001 | Profit | Cumulative | 3,000 | 33,800 | 19,800 | 36,800 | 73,800 | 121,800 |
B001 | Sales | Cumulative | 15,000 | 60,000 | 135,000 | 240,000 | 375,000 | 540,000 |
B001 | Cost | Cumulative | 10,500 | 19,700 | 103,700 | 186,700 | 279,700 | 395,200 |
B001 | Profit | Cumulative | 4,500 | 40,300 | 31,300 | 53,300 | 95,300 | 144,800 |
Thank you very much, Tracy
try using rangesum() and Above()
RangeSum(Above(TOTAL Sales, 0, RowNo(TOTAL)))
Dear Mr. Ogster
Above is used to compute Vertical but not Horizonal Cumulative, please note that I have tried the following but all not working :
1. RangeSum(Before(sum(Data), 0, NoOfRows()))
It is correct for Jan, Feb and Mar but wrong from Apr onwards.
2. RangeSum( before(TOTAL sum(Data),0,RowNo(TOTAL)))
No working at all.
Thank you, Tracy
use crosstable() load to transpose your data
Dear Mr Vinieme
No sure how your recommendation will help to resolve my issue on Rangesum(Before()). Also, I had already used crosstable() to transpose the data, please refer to the attachments in previous post.
Regards, Tracy
My guess why its not quite working is before the number of rows you have is 3 sales cost and profit rather than it stopping at Mar for some reason so lets try tweaking the formula a bit.
If you have your data stored with each date a field name. Id suggest remodelling it. but if that's your structure
RangeSum(Before(sum(Data), 0)
might work.