Horizontal Cumulative By Customer

[tracycrown]

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

[ogster1974]

try using rangesum() and Above()

RangeSum(Above(TOTAL Sales, 0, RowNo(TOTAL)))

[tracycrown]

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

[vinieme12]

use crosstable() load to transpose your data

https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/LoadData/work-with-c...

[tracycrown]

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

[ogster1974]

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.