cumulative total sales by month with nested conditions

[Anonymous]

Good day, 

I want to build in Qlik Sense a chart that shows cumulative sales. But there are some features because of which I do not understand how to do it:
1. The period does not contain dates, but names of months: M1, M2, M3, ....
2. Also in the data array contains unnecessary information that you want to exclude / filter.

As is:

Period	Product	Item	Amount
M1	Product_1	Item_1	10
M1	Product_1	Item_2	10
M1	Product_1	Item_3	10
M1	Product_2	Item_1	10
M1	Product_2	Item_2	10
M1	Product_2	Item_3	10
M2	Product_1	Item_1	10
M2	Product_1	Item_2	10
M2	Product_1	Item_3	10
M2	Product_2	Item_1	10
M2	Product_2	Item_2	10
M2	Product_2	Item_3	10
M3	Product_1	Item_1	10
M3	Product_1	Item_2	10
M3	Product_1	Item_3	10
M3	Product_2	Item_1	10
M3	Product_2	Item_2	10
M3	Product_2	Item_3	10
M4	Product_1	Item_1	10
M4	Product_1	Item_2	10
M4	Product_1	Item_3	10
M4	Product_2	Item_1	10
M4	Product_2	Item_2	10
M4	Product_2	Item_3	10

How need:

Period	Product	Item	Amount
M1	Product_1	Item_3	10
M1	Product_2	Item_3	10
M2	Product_1	Item_3	20
M2	Product_2	Item_3	20
M3	Product_1	Item_3	30
M3	Product_2	Item_3	30
M4	Product_1	Item_3	40
M4	Product_2	Item_3	40

P.s. RangeSum(Above(SUM(Amount),0,RowNo())) does't work correctly, pls, help.

[sunny_talwar]

Convert M1, M2 to Jan and Feb... 😀... You don’t know how your data looks like?

[Anonymous]

I know 🙂
Data is unloaded from the external system with codes: M1, M2, M3 ... and in MS Excel it is necessary to transform/translate it in Jan, Feb, Mar ... It would be great if this step could be skipped

[sunny_talwar]

I guess just use the if statement
If(Period = 'M1', 'Jan',
If(Period = 'M2', 'Feb', ....))

and then you can do this
Month(Date#(Period, 'MMM')) as Period

Or try this
Month(Date#(PurgeChar(Period, 'M'), 'M')) as Period

I guess you have a lot of options here... just have to decide what you wish to do.

[Anonymous]

thanks a lot for Help

[sunny_talwar]

No problem at all 🙂
I am glad I was able to help.

Best,
Sunny