Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
i'm pretty new with QlikView, figured out most things on my own or read here in this Community but I'm stuck in a problem which I can't figure out by my own ... but i guess it's pretty simple.
I need to sum up all counter values but with a distinct dimension field and without interfering with any other dimension/filter.
To clear things up, here a "basic raw data table". I have three dimensions with key-identifiers and 2 Counters:
Main_Event | Event_Category | Event_Detail | Counter_1 | Counter2 |
123 | 12 | 1 | 300 | 400 |
123 | 12 | 2 | 300 | 400 |
123 | 13 | 3 | 300 | 400 |
123 | 13 | 4 | 300 | 400 |
123 | 14 | 5 | 300 | 400 |
123 | 15 | 5 | 300 | 400 |
234 | 12 | 2 | 600 | 200 |
234 | 15 | 2 | 600 | 200 |
234 | 16 | 7 | 600 | 200 |
543 | 12 | 4 | 100 | 500 |
What I would like to see in a Pivot-Table is following: Sum-Up all the Counter-Values but "distinct" on the Main-Event
Event_Category | SUM(Counter_1) | SUM(Counter_2 |
SUM | 1000 | 1100 |
12 | 1000 | 1100 |
13 | 300 | 400 |
14 | 300 | 400 |
15 | 900 | 600 |
16 | 600 | 200 |
E.g.: SUM over all Categories are the unique values for each Main_Event (once Counter_1 = 300 + 600 + 100)
Event_Category 12 is 4 times in the raw data Table, twice in the Main Event 123 but I only want to use the Value for Counter_1=300 once for Calculation.
In Pivot-Table we add new Dimension Event_Detail, again with aggregation which should look like following:
Event_Category | Event_Detail | SUM(Counter_1) | SUM(Counter_2 |
12 | SUM | 1000 | 1100 |
12 | 1 | 300 | 400 |
12 | 2 | 900 | 600 |
12 | 4 | 100 | 500 |
13 | SUM | 300 | 400 |
13 | 3 | 300 | 400 |
13 | 4 | 300 | 400 |
14 | SUM | 300 | 400 |
14 | 5 | 300 | 400 |
15 | SUM | 900 | 600 |
15 | 2 | 600 | 200 |
15 | 5 | 300 | 400 |
16 | SUM | 600 | 200 |
16 | 7 | 600 | 200 |
If I enable all dimensions in the table, it would lead to following structure:
Event_Category | Event_Detail | Main_Event | SUM(Counter_1) | SUM(Counter_2 |
12 | SUM | 1000 | 1100 | |
12 | 1 | 123 | 300 | 400 |
12 | 2 | SUM | 900 | 600 |
12 | 2 | 123 | 300 | 400 |
12 | 2 | 234 | 600 | 200 |
12 | 4 | 543 | 100 | 500 |
13 | SUM | 300 | 400 | |
13 | 3 | 123 | 300 | 400 |
13 | 4 | 123 | 300 | 400 |
14 | SUM | 300 | 400 | |
14 | 5 | 123 | 300 | 400 |
15 | SUM | 900 | 600 | |
15 | 2 | 234 | 600 | 200 |
15 | 5 | 123 | 300 | 400 |
16 | SUM | 600 | 200 | |
16 | 7 | 234 | 600 | 200 |
I hope i got to the point with my explainations and that the tables clear up the problem.
Somewhere in the formular I need to add a "reference" for only using distinct "main_events", tried several different formulars, but i was far away from solving.
Many thanks in advance,
Robert Ehrenbert, Austria, Vienna
have a look at the attach example
Hi, thanks for the fast response, but I didn't used a good example I guess.
Your solution: "sum(DISTINCT Counter_1)" works in the case above but the values for Counter_1 are not distinct (well in the test example above they are, sorry).
To make my point clear, i just copied Main_Event=123 to Main_Event=666 and rebuild Tables:
Raw Data:
Main_Event | Event_Category | Event_Detail | Counter_1 | Counter2 |
123 | 12 | 1 | 300 | 400 |
123 | 12 | 2 | 300 | 400 |
123 | 13 | 3 | 300 | 400 |
123 | 13 | 4 | 300 | 400 |
123 | 14 | 5 | 300 | 400 |
123 | 15 | 5 | 300 | 400 |
234 | 12 | 2 | 600 | 200 |
234 | 15 | 2 | 600 | 200 |
234 | 16 | 7 | 600 | 200 |
543 | 12 | 4 | 100 | 500 |
666 | 12 | 1 | 300 | 400 |
666 | 12 | 2 | 300 | 400 |
666 | 13 | 3 | 300 | 400 |
666 | 13 | 4 | 300 | 400 |
666 | 14 | 5 | 300 | 400 |
666 | 15 | 5 | 300 | 400 |
And the last resulting Table should look like following:
Event_Category | Event_Detail | Main_Event | SUM(Counter_1) | SUM(Counter_2 |
SUM | SUM | SUM | 1300 | 1500 |
12 | SUM | SUM | 1300 | 1500 |
12 | 1 | SUM | 600 | 800 |
12 | 1 | 123 | 300 | 400 |
12 | 1 | 666 | 300 | 400 |
12 | 2 | SUM | 1200 | 1000 |
12 | 2 | 123 | 300 | 400 |
12 | 2 | 666 | 300 | 400 |
12 | 2 | 234 | 600 | 200 |
12 | 4 | SUM | 100 | 500 |
12 | 4 | 543 | 100 | 500 |
13 | SUM | SUM | 600 | 800 |
13 | 3 | SUM | 600 | 600 |
13 | 3 | 123 | 300 | 400 |
13 | 3 | 666 | 300 | 400 |
13 | 4 | SUM | 600 | 600 |
13 | 4 | 123 | 300 | 400 |
13 | 4 | 666 | 300 | 400 |
14 | SUM | SUM | 600 | 800 |
14 | 5 | 123 | 300 | 400 |
14 | 5 | 666 | 300 | 400 |
15 | SUM | SUM | 1200 | 1000 |
15 | 2 | SUM | 600 | 200 |
15 | 2 | 234 | 600 | 200 |
15 | SUM | SUM | 600 | 800 |
15 | 5 | 123 | 300 | 400 |
15 | 5 | 666 | 300 | 400 |
16 | SUM | SUM | 600 | 200 |
16 | 7 | 234 | 600 | 200 |
Take a closer Look to Event_Category 13 where all Counters are exactly the same but from two different Main_Events, so I need to add-up both Counters to the Event_Detail - SUM, but as both Main_Events are already used in the Event_Details, I don't want to add the two aggregate values of the Event_Details for the SUM of Event_Category ... somewhere in the formular there needs to be a {<distinct Main_Event>} entry, but i haven't found the correct place so far.
Thanks in advance and with best Regards,
Robert
Hallo,
anbei mein Vorschlag für die Lösung.
lg
Schlauss B.