Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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)
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
Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel | Follow me on LinkedIn
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)