Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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 |
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
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
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