Differences between subsets in an expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Customer_ID	Customer_Name	Transaction_Date	MonthName	Flag
9003	Mr Q	01/04/2016	Apr 2016	N
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

Re: Differences between subsets in an expression

swuehl — Wed, 21 Sep 2016 19:02:20 GMT

Try

=Count(DISTINCT {<MonthName ={'Apr 2016'}, Customer_ID = e({<MonthName = {'Mar 2016'}>})>}Customer_ID)

Re: Differences between subsets in an expression

Anonymous — Thu, 22 Sep 2016 08:52:13 GMT

Many thanks Stefan, that worked well!

Mit freundlichen Grüßen,

Sarah

topic Differences between subsets in an expression in QlikView

Differences between subsets in an expression

Re: Differences between subsets in an expression

Re: Differences between subsets in an expression