Conditional Cumulative Sum

schwerter — Tue, 07 Dec 2021 00:54:06 GMT

I have a table with the ammount of customers that canceled their account by the month of the purchase and how many months have passed since then until cancellation. The table looks like this:

MONTH	1	2	3	4	5	6	7	8	9	10
Ene-21	6	14	3	6	20	11	3	19	12	8
Feb-21	16	4	5	9	16	2	9	15	13
Mar-21	3	8	11	1	8	6	5	3
Abr-21	2	9	0	19	6	1	15
May-21	8	11	4	17	4	2
Jun-21	8	5	14	19	19
Jul-21	1	11	7	2
Ago-21	4	8	16
Set-21	0	9
Oct-21	5
Nov-21

I have calculated the total ammount of months (columns) that each sales month (row) should have. The resulting table should look like this:

MONTH	MONTHS	1	2	3	4	5	6	7	8	9	10	11	12
Ene-21	10	6	20	23	29	49	60	63	82	94	102	-	-
Feb-21	9	16	20	25	34	50	52	61	76	89	-	-	-
Mar-21	8	3	11	22	23	31	37	42	45	-	-	-	-
Abr-21	7	2	11	11	30	36	37	52	-	-	-	-	-
May-21	6	8	19	23	40	44	46	-	-	-	-	-	-
Jun-21	5	8	13	27	46	65	-	-	-	-	-	-	-
Jul-21	4	1	12	19	21	-	-	-	-	-	-	-	-
Ago-21	3	4	12	28	-	-	-	-	-	-	-	-	-
Set-21	2	0	9	-	-	-	-	-	-	-	-	-	-
Oct-21	1	5	-	-	-	-	-	-	-	-	-	-	-
Nov-21	0	-	-	-	-	-	-	-	-	-	-	-	-

Each cell of every column should cumulate the ammonut of customers on the previous columns as long as the month is lower o equal to the maximum numbers of months (column MONTHS).

I'm attaching an Excel file with an example of what I want to replicate in Qlik Sense.

The formula I'm using is:

IF(EXIT_MONTH<="$(vMaxMonths)",COUNT({<EXIT_MONTH={">0<=1"}>} CUSTOMER_ID),'-')

I'm repeating this formula in every column changing COUNT({<EXIT_MONTH={">0<=1"}>} CUSTOMER_ID) with the corresponding month.

For month 2: IF(EXIT_MONTH<="$(vMaxMonths)",COUNT({<EXIT_MONTH={">0<=2"}>} CUSTOMER_ID),'-')
For month 6: IF(EXIT_MONTH<="$(vMaxMonths)",COUNT({<EXIT_MONTH={">0<=6"}>} CUSTOMER_ID),'-')

But this formula isn't working.

Thank you in advance.

topic Conditional Cumulative Sum in App Development

Conditional Cumulative Sum