Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
k_t_12
Contributor II
Contributor II

Getting a COUNT from an IF expression

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
Labels (3)
1 Solution

Accepted Solutions
Chanty4u
MVP
MVP

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)

 

View solution in original post

2 Replies
JandreKillianRIC
Partner Ambassador
Partner Ambassador

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

Chanty4u
MVP
MVP

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)