topic Re: Aggregated Distinct Sum in QlikView

Aggregated Distinct Sum

jobsjoe123 — Sat, 16 Nov 2024 02:22:35 GMT

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?

Re: Aggregated Distinct Sum

dplr-rn — Thu, 26 Sep 2019 18:13:54 GMT

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

Re: Aggregated Distinct Sum

asinha1991 — Thu, 26 Sep 2019 18:53:19 GMT

maybe this

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

Re: Aggregated Distinct Sum

Brett_Bleess — Mon, 07 Oct 2019 21:56:30 GMT

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/SetAnalysis/set-analysis-expressions.htm

Regards,
Brett