Need Help: Sum/Aggr of Counters for a distinct dimension in Pivot-Table

[#1]

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

[lironbaram]

have a look at the attach example

[#3]

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

[schlaussb · Accepted solution]

Hallo,

anbei mein Vorschlag für die Lösung.

lg

Schlauss B.