topic How to Aggregate based on Dimension in QlikView

How to Aggregate based on Dimension

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

Hi,

I need to make a summary based on following data.

Need Sales Man wise Stores count those purchased 2 products.

I already tried Aggr() and expression search like Count({$<Store_ID={"=count(Distinct Product_ID)=2"}>} Distinct Store_ID) but not succeeded.

Store_ID	Sales_Man_ID	Sales_Man	Year	Month	Week_No	Product_ID	Net_Sales_Units
T98987	A001	Sales Man1	2016	August	1	B608887	500
T98987	A001	Sales Man1	2016	August	1	B608888	150
T10098	A001	Sales Man1	2016	August	1	C785421	245
T10098	A001	Sales Man1	2016	August	1	D875498	652
T10098	A001	Sales Man1	2016	August	1	E546598	142
T15487	A001	Sales Man1	2016	August	1	D875498	225
T15487	A001	Sales Man1	2016	August	1	E546598	335

Required summary is like below

Sales_Man_ID	Sales_Man	Year	Month	Week_No	Store_Cout
A001	Sales Man1	2016	August	1	2

Please support.

Regards,

Imran Khan

Re: How to Aggregate based on Dimension

swuehl — Sat, 10 Sep 2016 20:08:54 GMT

Doesn't the expression

Count({$<Store_ID={"=count(Distinct Product_ID)=2"}>} Distinct Store_ID)

return the required outcome using the sample data you've posted?

I guess the problem will show up if you add more Sales_Man_ID, Year, Month etc. values, right?

You could create a key in your script with the right granularity for your set analysis filter, like

LOAD Store_ID,

Sales_Man_ID,

Sales_Man,

Year,

Month,

Week_No,

Product_ID,

Net_Sales_Units,

AutoNumberHash256(Sales_Man_ID,Year,Month,Week_No,Store_ID) as Key

FROM

[https://community.qlik.com/thread/232247]

(html, codepage is 1252, embedded labels, table is @1);

Then use Key instead of Store_ID in your set expression (or adapt the Key definition to your needs):

Sales_Man_ID	Sales_Man	Year	Month	Week_No	Count({$<Key={"=count(Distinct Product_ID)=2"}>} Distinct Store_ID)
					2
A001	Sales Man1	2016	August	1	2

Re: How to Aggregate based on Dimension

effinty2112 — Sun, 11 Sep 2016 08:31:07 GMT

Hi Imran,

What about:

Sales_Man_ID	Sales_Man	Year	Month	Week_No	=Sum(Aggr(if(Count(DISTINCT Product_ID)=2,1),Store_ID,Sales_Man_ID,Year,Month,Week_No))
					2
A001	Sales Man1	2016	August	1	2

Kind regards

Andrew

Re: How to Aggregate based on Dimension

Anonymous — Sun, 11 Sep 2016 15:01:57 GMT

Thanks a billion.....

What a brilliant finding and accurate solution.

By creating Key AutoNumberHash256(Sales_Man_ID,Year,Month,Week_No,Store_ID) as Sales_Key and by using Sales_Key in set expression Count({$<Sales_Key={"=count(Distinct Product_ID)=2"}>} Distinct Sales_Key) now I am getting correct results.

Thanks again for quick solution.

Regards,

Imran Khan

Re: How to Aggregate based on Dimension

Anonymous — Sun, 11 Sep 2016 15:04:38 GMT

Hi Andrew,

Problem has been resolved as suggested by Stefan Wühl for creating Key and using that key into set expression.

Regards,

Imran Khan