Include DISTINCT in set analysis

[#1]

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] )

[SunilChauhan]

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

hope this helps

[#3]

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

[Gysbert_Wassenaar]

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.

[#5]

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.

[Gysbert_Wassenaar]

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