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]

You want to only look at Item_3? Also, Period is M1, M2, M3 or Jan, Feb, Mar?

[Anonymous]

You want to only look at Item_3 - Yes
Period is M1, M2, M3 or Jan, Feb, Mar? -it does not matter

[Anonymous]

probably better Jan, Feb, Mar,... - so it is more clearly

[sunny_talwar]

I was not asking if you want Period to be M1, M2, M3 or Jan, Feb, Mar... I was asking what data do you have? Do you have M1, M2, M3 in your data or Jan, Feb, Mar? 🙂

[Anonymous]

Sorry, 🙂
I have Jan, Feb, Mar, ...

[sunny_talwar · Accepted solution]

First thing first, I am not sure if your Month field is read as a dual field or not, but in case it doesn't it would make sense to make sure that it is read as a dual field... to do this... you can try like this

LOAD Month(Date#(Period, 'MMM')) as Period

Doing the above will give your Month an underlying numerical value. Jan will be 1, Feb will be 2 and so on...

Once you have this... you can try this

Aggr(RangeSum(Above(Sum({<Item = {"Item_3"}>} Amount), 0, RowNo())), Product, (Period, (NUMERIC)))

[Anonymous]

Not help 😞

[sunny_talwar]

Seems strange that Jan is repeating twice...

I am guessing you understand your data... do you know why the months are repeating?

[Anonymous]

Thanks, Double data source. Now it works.
But what about the situation if M1, M2,... M12?