Need Help: Sum/Aggr of Counters for a distinct dimension in Pivot-Table
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.
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.
