topic Re: How to implement the set analysis in set analysis in App Development

How to implement the set analysis in set analysis

Alyona — Tue, 30 Nov 2021 18:17:33 GMT

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?

Re: How to implement the set analysis in set analysis

avinashelite — Wed, 07 Jul 2021 07:40:42 GMT

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

Re: How to implement the set analysis in set analysis

mikaelsc — Wed, 07 Jul 2021 08:22:35 GMT

isn't this just a double enquotation issue?

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

Re: How to implement the set analysis in set analysis

Alyona — Wed, 07 Jul 2021 11:06:31 GMT

@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.

Re: How to implement the set analysis in set analysis

Alyona — Wed, 07 Jul 2021 11:09:39 GMT

@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 😞

Re: How to implement the set analysis in set analysis

tresB — Wed, 07 Jul 2021 11:58:33 GMT

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])

Re: How to implement the set analysis in set analysis

mikaelsc — Wed, 07 Jul 2021 11:59:03 GMT

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])

Re: How to implement the set analysis in set analysis

Alyona — Wed, 07 Jul 2021 12:33:23 GMT

This works! Many-many thanks! 🙂

Re: How to implement the set analysis in set analysis

Alyona — Wed, 07 Jul 2021 12:34:58 GMT

This solution also works! Many thank! 🙂

Re: How to implement the set analysis in set analysis

Lakshmi22it — Fri, 12 Sep 2025 10:17:49 GMT

Can you please explain me this code i wanna learn 🙂

Re: How to implement the set analysis in set analysis

Lakshmi22it — Fri, 12 Sep 2025 10:18:23 GMT

Can you please explain me this code i wanna learn 🙂