QlikView Countifs comparing current and previous month data

JoelTanSG — Tue, 25 Oct 2022 08:43:33 GMT

Hi All

I've a table in QlikView:

ID	Date	Category
1	12/2021	A
2	12/2021	B
3	12/2021	B
1	01/2022	A
2	01/2022	B
3	01/2022	C
1	02/2022	B
2	02/2022	B
3	02/2022	A

I want to output the count of distinct IDs that was "A" in the previous month (Jan) and is now "B" in the current month (Feb).

so by right the only ID that satisfy the condition would be ID: 1 and the output should be 1

This is what I did and its not working:

//current month data
count(DISTINCT{$<[Date]={"$(=Max(Date))"},category={"A"}>}ID)
and
// previous month data
count(DISTINCT{$<[Date]={"$(=date((AddMonths(Max(Date),-1)),'MM-YYYY'))"},category={"B"}>}ID)

topic QlikView Countifs comparing current and previous month data in QlikView

QlikView Countifs comparing current and previous month data