3 Replies Latest reply: Oct 6, 2016 1:12 PM by Sunny Talwar RSS

    AGGR function with multiple dimensions

    Herbert van Hoogdalem

      Hi all,

       

      There is something I just don't get about a certain AGGR expression I am using.

       

      Consider the following pivot-table:

       

      1.png

      I use the COUNT(EMPLID) expression to get a simple line count.

      I use 3 dimensions, Onderwerp SchooljaarExamen and CijferCategorie.

      It works perfectly.

       

      I am however more interested in the relative amount per Onderwerp/Schooljaar so I need the total per schooljaar per onderwerp

      Like this:

      2.png

      As you can see it totals the amount per year nicely per onderwerp.

       

      Ok, the standard solution is to use an aggr function to aggregate the same result.

      Like this:

       

      3.png

      Which yield exactly the same table.

       

      Almost there I thought, now I just have to reintroduce the CijferCategorie-dimension, and it should (imho) yield the same totals. If that works I can use the AGGR expression in the first table.

      But then I get this:

      4.png

      The totals pop up in totally unpredictable fields. Dimensions partially disappear...

      Even if I change the expression to =AGGR(COUNT( EMPLID), SchooljaarExamen, Cijfercategorie, Onderwerp) I get the same result.

      I just don't get what's happening here.

       

      The questions:

       

      1. Can I get it to work using this train of thought?

      2. Or is there perhaps an easier way to change the numbers in the first table to relative per year/cijfercategorie

       

      Thanks

       

      Herbert