How to implement the set analysis in set analysis

[Alyona]

Hi Qlik experts!

I need your help! 

I have the calculation of [Volume] based on selection in [Date] field as this one: 

sum({<[Date]={">=$(=yearstart(max([Date]))) <=$(=max([Date]))"} > } [Volume])

But now I need to calculate the count of distinct customers which have the volume above. 

I tried something like this: 

count({<[Customer ID] = {"= sum({<[Date]={">=$(=yearstart(max([Date]))) <=$(=max([Date]))"} > } [Volume]) > 0"}>} distinct [Customer ID])

But it doesn't work.  Also I tried to use variable for calculation the sum of volume and to use it the count calculation, but it was also without success. 

Could anybody help me, how to implement this calculation? 

[avinashelite]

Could you please help to share what is the results your getting ? 

Please share the sample data so that we could also work on the solution 

[mikaelsc]

isn't this just a double enquotation issue? 

count({<[Customer ID] = {"= sum({<[Date]={"">=$(=yearstart(max([Date]))) <=$(=max([Date]))""} > } [Volume]) > 0"}>} distinct [Customer ID])

[Alyona]

 @avinashelite hi!

Let's imagine that we have the following table with data: 

Customer ID	Date	Volume
Customer A	1-Apr-20	100
Customer A	1-Feb-21	150
Customer A	1-Sep-21	120
Customer B	1-Mar-21	50
Customer B	1-May-21	75
Customer B	1-Sep-21	40
Customer C	1-Nov-20	90
Customer C	1-Dec-20	60

Different cases:

1) If in the [Date] I select for example current date - 07-Jul-21 then I will expect to see in: 

• Volume = sum of [Volume] in records where the [Date] in the range from [1-Jan-21] till [07-Jul-21]  = 275
• Customer count = distinct [Customer ID] which have the volume in the range of dates [1-Jan-21] till [07-Jul-21] = 2 (Customer A and Customer B)

Customer ID	Date	Volume
Customer A	1-Feb-21	150
Customer B	1-Mar-21	50
Customer B	1-May-21	75

2) If in the [Date] I select for example 31-Dec-20 then I will expect to see in: 

• Volume = sum of [Volume] in records where the [Date] in the range from [1-Jan-20] till [31-Dec-20]  = 250
• Customer count = distinct [Customer ID] which have the volume in the range of dates [1-Jan-20] till [31-Dec-20] = 2 (Customer A and Customer C)

Customer ID	Date	Volume
Customer A	1-Apr-20	100
Customer C	1-Nov-20	90
Customer C	1-Dec-20	60

3) If in the [Date] I select for example 01-Aug-20 then I will expect to see in: 

• Volume = sum of [Volume] in records where the [Date] in the range from [1-Jan-20] till [01-Aug-20]  = 100
• Customer count = distinct [Customer ID] which have the volume in the range of dates [1-Jan-20] till [01-Aug-20] = 1 (Customer A)

Customer ID	Date	Volume
Customer A	1-Apr-20	100

Also I created and attached small app with example of data. Is it enough as the example? 

If "no" then please let me know what do you prefer to have. 

[Alyona]

@mikaelsc hi! I tried to use double quotation, it works only without any selection in [Date] field. If I select any Date then I always receive 0 as customer count 😞 

[tresesco]

Try putting the date fields in the outer count(), like:

count({<Date, Month, [Customer ID] = {"= sum({<[Date]={"">=$(=yearstart(max([Date]))) <=$(=max([Date]))""} > } [Volume]) > 0"}>} distinct [Customer ID]) 

[mikaelsc · Accepted solution]

adapt your expression in order to consider the "selected" date range in the "count"... otherwise you're excluding all your records that don't fall on the selected date

count({<[Date]={">=$(=yearstart(max([Date]))) <=$(=max([Date]))"},[Customer ID] ={"=sum({<[Date]={"">=$(=yearstart(max([Date]))) <=$(=max([Date]))""}>}[Volume])>0"}>} distinct [Customer ID])

[Alyona]

This works! Many-many thanks! 🙂

[Alyona]

This solution also works! Many thank! 🙂