topic Include DISTINCT in set analysis in QlikView

Include DISTINCT in set analysis

Mon, 13 Apr 2015 15:49:26 GMT

How do I modify the following to include DISTINCT UID? I tried aggr() and UID=DISTINCT UID but could not come up with a working solution. (UID is the first column)

UID	Report Category	MonthlyChangedRC	Month EGM
1	Cancelled	Forecasted	0.0348
1	Realized	Forecasted	0.0348
1	Cancelled	Forecasted	0.0348
1	Cancelled	Forecasted	0.0348
2	Cancelled	Opportunity	2.313
2	Cancelled	Opportunity	2.313
2	Cancelled	Opportunity	2.313
3	Cancelled	Forecasted	-0.012
4	Realized	Forecasted	0.0944

sum ({<[~Week Number] ={'1','2','3','4'}, [Report Category]-={'Pre-Pipeline','Identified','Opportunity','Forecasted'}, MonthlyChangedRC={'Pre-Pipeline','Identified','Opportunity','Forecasted'}>} [Month EGM] )

Re: Include DISTINCT in set analysis

SunilChauhan — Mon, 13 Apr 2015 15:55:46 GMT

hope this helps

Re: Include DISTINCT in set analysis

Mon, 13 Apr 2015 15:58:34 GMT

I thought of that except I have thousands of records and some could have the same value for [Month EGM] I really need to group it by UID because that is the true unique identifier

Re: Include DISTINCT in set analysis

Gysbert_Wassenaar — Mon, 13 Apr 2015 16:08:13 GMT

Can you explain what you're really trying to calculate? It sounds like you've already decided on a solution, but what is the actual problem that your solution should solve? Perhaps there are other solutions that will solve that problem more easily.

Re: Include DISTINCT in set analysis

Mon, 13 Apr 2015 17:13:18 GMT

I need to find the sum of [Month EGM] where items have moved into a particular Report Category. The Report Category column has the original Report Category and the MonthlyChangedRC has the new Report Category. However, I am looking across 5 weeks of data so there are some items that are being counted multiple times because they exist in multiple weeks and may have moved one or more times in different weeks.

Re: Include DISTINCT in set analysis

Gysbert_Wassenaar — Wed, 15 Apr 2015 11:06:22 GMT

Can you post a qlikview document with data? That way we can try to create a solution for you. Read this document if you're worried about confidential information: Preparing examples for Upload - Reduction and Data Scrambling