Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
maybe this
sum(REVENUE)/COUNT(DISTINCT [Element Name]&[Anchor ID])
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
Regards,
Brett