Calculate unsubscribe status

twanqlik — Wed, 05 May 2021 16:44:57 GMT

I want to check when a customer unsubscribes.

Input table:

ID	MONTH	Subscribed status
1	2021-05	Yes
1	2021-04	No
1	2021-03	No
1	2021-02	Yes
1	2021-01	Yes
1	2020-12	No
1	2020-11	Yes
1	2020-10	Yes

Expected result:

ID	MONTH	Subscribed status
1	2021-05
1	2021-04
1	2021-03	Unsubscribed
1	2021-02
1	2021-01
1	2020-12	Unsubscribed
1	2020-11
1	2020-10

Any tips on how to accomplish this?

The ordering of the months is of course important.

In the expected output table are only 2 Unsubscribed statuses, not 3.
The customer unsubscribed in March 2021, and was still unsubscribed in April 2021.
The action was performed in March, and that i want to visualize.

Re: Calculate unsubscribe status

JuanGerardo — Wed, 05 May 2021 17:01:04 GMT

Hi @twanqlik, I think you can accomplish this with Previous() function:

NewTable:

Load

ID,

MONTH,

If(ID = Previous(ID) And Previous([Suscribed status]) = 'Yes' And [Suscribed status] = 'No', 'Unsubscribed') AS [Suscribed status]

Resident InputTable // or your datasource

Order by ID, MONTH;

topic Re: Calculate unsubscribe status in QlikView

Calculate unsubscribe status

Re: Calculate unsubscribe status