Aggregated Distinct Sum

[jobsjoe123]

Hello guys, 

I have data in the form 

Anchor ID	Anchor Name	Element ID	Element Name	Jan-19	Feb-19	Mar-19	Apr-19	May-19	Jun-19	Jul-19	Aug-19
123456789	A	124	Element4	10	15	0	10	15	10	15	0
123456789	A	123	Element3	5	10	10	5	20	25	10	10
123456789	A	122	Element2	30	10	10	5	10	5	5	10
122245678	B	124	Element4	10	15	0	10	15	10	15	0
133332213	C	122	Element2	30	10	10	5	10	5	5	10
133332213	C	121	Element1	0	0	10	15	15	20	10	15
144444444	D	125	Element5	7	7	7	4	5	6	7	15
144444444	D	126	Element6	15	22	24	30	22	10	2	5

Using a crosstable, I've transformed the values into a REVENUE column. Each element has a revenue for each month, and is tied to an Anchor.   Now I want to sum the revenue for each Element, without repeating the revenue numbers for Elements that are tied to multiple anchors. 

So Ideally, i want to write a set analysis that results in a value of 528, after aggregating the unique revenue values for each Element . See below:

Element 1	85
Element 2	85
Element 3	95
Element 4	75
Element 5	58
Element 6	130
	528

How do I go about this please?

[dplr-rn]

that will be tricky as is.

I would suggest splitting the table into 2

1 with the Anchor and Element Association

the other with the actual data but you should remove anchor columns and run a distinct on it.

Hope that nudge helps 

[asinha1991]

maybe this

sum(REVENUE)/COUNT(DISTINCT [Element Name]&[Anchor ID])

[Brett_Bleess]

If either of the other posts helped, be sure to use the Accept as Solution button to give them credit on whichever post did help.  If you did figure something else out, consider posting that and mark it so others will know the solution in the future.  

The best I have to try to help out a bit is a Design Blog post on Set Analysis.  There are further links at the bottom of the post too, hopefully this may help if you are still trying to figure out the details, and I am going to include Help link too just in case.

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Regards,
Brett