3 Replies Latest reply: May 13, 2014 8:48 AM by Bernhard Schlauss RSS

    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

          • 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