Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to Aggregate based on Dimension

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_IDSales_Man_IDSales_ManYearMonthWeek_NoProduct_IDNet_Sales_Units
T98987A001Sales Man12016August1B608887500
T98987A001Sales Man12016August1B608888150
T10098A001Sales Man12016August1C785421245
T10098A001Sales Man12016August1D875498652
T10098A001Sales Man12016August1E546598142
T15487A001Sales Man12016August1D875498225
T15487A001Sales Man12016August1E546598335

Required summary is like below

Sales_Man_IDSales_ManYearMonthWeek_NoStore_Cout

A001
Sales Man12016August12

Please support.

Regards,

Imran Khan

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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
A001Sales Man12016August12

View solution in original post

4 Replies
swuehl
MVP
MVP

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
A001Sales Man12016August12
effinty2112
Master
Master

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
A001Sales Man12016August12

Kind regards

Andrew

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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