Getting a COUNT from an IF expression

[k_t_12]

Hi all,

New to Qlik Sense and needing some assistance.

I have a dataset that looks like the following:

Home Store ID	Customer ID	Home_Store_closure_date	Max_Purchase_date	CSTMR_STUS
1	123	1/5/2025	1/1/2025	MISSING
1	111	1/5/2025	1/7/2025	REPORTING
1	222	1/5/2025	1/15/2025	REPORTING
2	333	12/31/2024	12/5/2024	MISSING
2	444	12/31/2024	12/1/2024	MISSING

I created the following expression (CSTMR_STUS) which works as expected:

IF(MAX(Max_Purchase_date)>Home_Store_closure_date,'REPORTING','MISSING')

Context for this table - each customer is assigned a 'Home Store'. In this table, all home stores have closed by a specific date, indicated by 'HOME_STORE_CLOSURE_DATE'.

'MAX_PURCHASE_DATE' reflects the date of the most recent purchase by that customer. This MAX_PURCHASE_DATE includes data from many stores, not just the Home Store. If we have not seen any purchase from a customer since their respective home store's closure, we consider them "missing". 

If we have seen a purchase from a customer that is after their respective Home Store's closure, we consider them 'Reporting' as they are now purchasing at a different location. 

I would like to simply get a count of Customer IDs that are considered "MISSING" and "REPORTING". How can I modify the expression to get those two counts? 

In a table where I add Home Store ID as a Dimension, I'd like an expected output like as follows: 

Home Store ID	# of Reporting Customers	# of Missing Customers
1	2	1
2	0	2

[JandreKillianRIC]

Hi @k_t_12 

Doing this on my mobile quickly. Please check and let me know.

Sum(aggr(IF(MAX(Max_Purchase_date)>Home_Store_closure_date,1,0), Customer ID, Home Store ID,Max_Purchase_date)) 

This is for missing

Sum(aggr(IF(MAX(Max_Purchase_date)>Home_Store_closure_date,0,1, Customer ID, Home Store ID,Max_Purchase_date)) 

This is for Reporting

You might need to add the Home_Store_closure_date after the max purchase date... Like this 

Sum(aggr(IF(MAX(Max_Purchase_date)>Home_Store_closure_date,0,1, Customer ID, Home Store ID,Max_Purchase_date,Home_Store_closure_date)) 

Regards Jandre

[Chanty4u · Accepted solution]

For missing customer 

COUNT({<Customer_ID={"=MAX(Max_Purchase_date) <= Home_Store_closure_date"}>} Customer_ID)

For reporting 

COUNT({<Customer_ID={"=MAX(Max_Purchase_date) > Home_Store_closure_date"}>} Customer_ID)