topic Re: How to calculate aggregated count using set analysis in App Development

How to calculate aggregated count using set analysis

AmitSah — Wed, 22 Dec 2021 15:42:54 GMT

Hi Experts,

Below is my sample table structure, where I want to calculate the count of customers which are new and which are existing using some scripting in the front end. like using set analysis and Agg function.

Sales_ID	Sales_Line_ID	Store_ID	Date_ID	PLU_ID	Time_ID	Quantity	Net_Sales	Gross_Sales	Customer_ID
89692	1	29	20170702	672	860	1	423.73	498.931	1043
89692	2	29	20170702	226	860	1	186.44	219.529	1043
89692	3	29	20170702	223	860	1	254.24	299.362	1043
89692	4	29	20170702	239	860	4	50.84	59.863	1043
89692	5	29	20170702	813	860	1	1058.47	1246.321	1043
133787	1	29	20170702	242	979	1	72.03	69.995	1043
133535	1	4	20160816	239	963	28	420	300.018	1008
106227	1	22	20170702	666	835	1	593.22	695.589	1043
106227	2	22	20170702	243	835	1	72.03	84.46	1043
88478	1	10	20170702	249	825	1	169.49	199.998	1043
116947	1	10	20170703	249	1203	1	169.49	199.998	1043
138761	1	1	20160523	628	737	1	1073	603.826	47848
138761	2	1	20160523	101	737	1	427	228.764	47848
138761	3	1	20160523	199	737	1	450	241.086	47848
106499	1	1	20160523	125	747	1	1200	642.897	47848
106499	2	1	20160523	267	747	1	436	245.357	47848
106499	3	1	20160523	367	747	1	234	125.365	47848
106499	4	1	20160523	243	747	1	80	42.86	47848
106499	5	1	20160523	391	747	1	407	229.037	47848
106499	6	1	20160523	570	747	1	63	33.752	47848
106499	7	1	20160523	571	747	1	80	42.86	47848
105030	1	1	20160523	748	807	1	1073	603.826	47848
105030	2	1	20160523	101	807	1	427	228.764	47848
105030	3	1	20160523	199	807	1	450	241.086	47848
84151	1	2	20170701	236	1125	1	101.69	119.994	1043
112115	1	3	20170702	236	829	1	101.69	119.994	1043

So, my output should be like:

one measure will give me New Customer Count i.e in the above case it is 1 i.e Customer_ID=1008 who visited only once.

and another measure will give me existing customer count i.e in the above case it is 2 i.e Customer_id (1043 and 47848) who bought items multiple times and on different dates or same dates, for that different Sales_id is generated for those purchase.

I can do that calculation by creating an aggregate table in the data model but I want that calculation to handled in the front end.

for example SQL to aggregate Customer Count:

select a.Customer_ID ,sum(a.customer_count) Cutsomer_Count,sum(a.net_sales) Net_Sales
from(
select count( distinct a.customer_id) customer_count,Store_ID,a.Date_ID,a.Sales_ID,a.Customer_ID,sum(a.net_sales) net_sales
from [dbo].[fact_sales_detailed] a
group by Store_ID,a.Date_ID,a.Sales_ID,a.Customer_ID
) a
group by a.Customer_ID

With this I can easyly get the Cutsomers who are new and existing.

But how can I do the same in front end calculation.

Any Help will be appreciated.

Thanks,

Amit

Re: How to calculate aggregated count using set analysis

settu_periasamy — Fri, 14 Feb 2020 17:15:17 GMT

May be try like

Sum(Aggr(Count(Distinct customer_id),Store_ID,Date_ID,Sales_ID,Customer_ID))

Re: How to calculate aggregated count using set analysis

AmitSah — Mon, 17 Feb 2020 09:57:03 GMT

Hi Settu,

I am new to Qlik Sense!

Thanks for the response, Yeah I had tried that out but That is giving me the total distinct customer count. But I want 2 different measure which will be as below:

1. New Customers (Measure) - i.e the count should be <=3

2. Existing customer (Measure) - i.e the count should be >3

How to give condition to filter to get below expression which will give above 2 measures:

Sum( Aggr(Count(distinct Customer_ID_Fact), Store_ID,Date_ID,Customer_ID_Fact,Sales_ID))

because when I am trying to use set analysis using the above expression then it is not validating.

Could you please help me with the above two-measure expression.

Attached is a Sample QVF file. If you will add customer ID on the Table in Sheet then you can see the customers who's count are more than 3 and who's count are <=3.

Thanks,

Amit

Re: How to calculate aggregated count using set analysis

AmitSah — Mon, 17 Feb 2020 09:59:38 GMT

Hi Settu,

I am new to Qlik Sense!

Thanks for the response, Yeah I had tried that out but That is giving me the total distinct customer count. But I want 2 different measure which will be as below:

1. New Customers (Measure) - i.e the count should be <=3

2. Existing customer (Measure) - i.e the count should be >3

How to give condition to filter to get below expression which will give above 2 measures:

Sum( Aggr(Count(distinct Customer_ID_Fact), Store_ID,Date_ID,Customer_ID_Fact,Sales_ID))

because when I am trying to use set analysis using the above expression then it is not validating.

Could you please help me with the above two-measure expression.

Attached is a Sample QVF file. If you will add customer ID on the Table in Sheet then you can see the customers who's count are more than 3 and who's count are <=3.

Thanks,

Amit

Re: How to calculate aggregated count using set analysis

AmitSah — Tue, 18 Feb 2020 08:02:24 GMT

Hi @settu_periasamy

Did you get a chance to look into this?

Hi, @sunny_talwar Any Idea on this from your end?

Any help will be appreciated.

Thanks,

Amit