Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to calculate the difference in counts of customers between two sets in an expression i.e. comparing two months worth of data:
Customer_ID | Customer_Name | Transaction_Date | MonthName | Flag |
---|---|---|---|---|
1234 | Mr X | 01/03/2016 | Mar 2016 | N |
5678 | Mrs Y | 01/03/2016 | Mar 2016 | Y |
1435 | Mr Z | 12/03/2016 | Mar 2016 | N |
4321 | Mr A | 12/03/2016 | Mar 2016 | Y |
5371 | Mrs B | 31/03/2016 | Mar 2016 | N |
7671 | Ms C | 31/03/2016 | Mar 2016 | N |
9003 | Mr Q | 01/04/2016 | Apr 2016 | N |
Customer_ID | Customer_Name | Transaction_Date | MonthName | Flag |
---|---|---|---|---|
1234 | Mr X | 01/04/2016 | Apr 2016 | N |
4321 | Mr A | 12/04/2016 | Apr 2016 | Y |
7671 | Ms C | 30/04/2016 | Apr 2016 | N |
9897 | Ms P | 30/04/2016 | Apr 2016 | Y |
I'm essentially trying to calculate the difference in the number of customers that appear in April that were not present in March. I'm finding it difficult to structure the syntax in this expression however as a distinct count of set B - set A. Would any Qlik users out there have any experience in trying to put together something similar?
I have one calculation in my sheet that calculates a distinct count of customer ID in Set B and subtracts it from Set A, i.e. Count(DISTINCT{<MonthName = {"Apr 2016"}>}Customer_ID)- Count(DISTINCT{<MonthName = {"Mar 2016"}>}Customer_ID), but here I want to subtract the rows in Set B where the Customer_ID or Customer_Names are identical between the two months and then perform a distinct count on the remaining rows, i.e. the answer should be 2 if Mr X, Mr A and Ms C are excluded.
Many thanks,
Sarah
Try
=Count(DISTINCT {<MonthName ={'Apr 2016'}, Customer_ID = e({<MonthName = {'Mar 2016'}>})>}Customer_ID)
Many thanks Stefan, that worked well!
Mit freundlichen Grüßen,
Sarah