0 Replies Latest reply: Nov 9, 2017 5:49 AM by Bernhard Pammer RSS

    Line Chart with 2 dimensions and one measure

    Bernhard Pammer

      Hi all experts out there,

       

      hope someone can help me out with my current question:

       

      I try to set up a line chart with 2 dimensions and one measure but I do not get the correct result I need.

       

      My basis date is structured as following:

       

      DATE    |  ID  |  DURATION | EVENT | ITEM

      01/2015| 100 |           0         | Start     | ITEM1

      01/2015| 101 |           -         | Start     | ITEM1

      01/2015| 102 |           -         | Start     | ITEM1

      01/2015| 103 |           -         | Start     | ITEM1

      01/2015| 104 |           1         | Start     | ITEM1

      01/2015| 105 |           2         | Start     | ITEM1

      01/2015| 106 |           2         | Start     | ITEM1

      01/2015| 107 |           -         | Start     | ITEM1

      01/2015| 108 |           -         | Start     | ITEM1

      ...

      02/2015| 110 |           0         | Start     | ITEM1

      02/2015| 111 |           -         | Start     | ITEM1

      02/2015| 112 |           -         | Start     | ITEM1

      02/2015| 113 |           -         | Start     | ITEM1

      02/2015| 114 |           1         | Start     | ITEM1

      02/2015| 114 |           2        | Start     | ITEM1

      02/2015| 115 |           2         | Start     | ITEM1

      02/2015| 116 |           2         | Start     | ITEM1

      02/2015| 117 |           -         | Start     | ITEM1

      02/2015| 118 |           -         | Start     | ITEM1

      ...

       

      My Dimensions are DATE and DURATION.

       

      My Measure should be Count(DURATION)/Count(Distinct(ID) which looks in table form like this:

       

      DATE    | DURATION | Count(DURATION) | Count(EVENT) | Count(DURATION)/Count(Distinct(ID))

      01/2015 |          0         |           1                   |           9              |                      11,1%

      01/2015 |          1         |           1                   |           9              |                      11,1%

      01/2015 |          2         |           2                   |           9              |                      22,2%

      02/2015 |          0         |           1                   |           10            |                      10,0%

      02/2015 |          1         |           1                   |           10            |                      10,0%

      02/2015 |          2         |           3                   |           10            |                      30,0%

       

       

      The final result should then look like:

       

      At the moment I've the problem, that for the 2nd dimension I do not get the correct total Count(Distinct(ID)) per month. Either I can choose with Count(Total(Distinct(ID)) all ID's (but not per month) or I get a wrong result, because due to the 2nd dimension my table above is automatically split up. Is there any way to avoid this?

       

      Any help would be appreciated.