Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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.

Many thanks in advance,

Robert Ehrenbert, Austria, Vienna

1 Solution

Accepted Solutions
schlaussb
New Contributor III

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

Hallo,

anbei mein Vorschlag für die Lösung.

lg

Schlauss B.

3 Replies
lironbaram
Honored Contributor II

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

have a look at the attach example

Not applicable

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

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
New Contributor III

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

Hallo,

anbei mein Vorschlag für die Lösung.

lg

Schlauss B.

Community Browser