2 Replies Latest reply: Jul 18, 2017 8:24 AM by Stefan Vind RSS

    how to sum based on distinct dimension

    Stefan Vind

      Hi guys,


      I am trying to get a function to work, where I essentially want the sum to be calculated based on a key which needs to be distinct.


      Table looking like this:


      ID  Type    Component Group     Actual     ValueKEY

      X   Cost     Internal                      0,85          X0,85

      Y   Cost     Internal                      0,85          Y0,85

      X  Target   Internal                      1,25          X1,25

      Y  Target   Internal                      1,25          Y1,25



      What I am trying to achieve is that it is possible to calculate the actuals once based on the dimension "ValueKEY", so that for both these instances the result will become:


      ID     Actual     Target

      X     0,85          1,25

      Y     0,85          1,25


      I've tried to set it up like this, where YTD_TY is an expression for the fiscal year, and the vCostComponents are the cost components and vAccum = "RangeSum(Above(", and v/Accum = ,"0,RowNo(TOTAL)))" that I am interested in:


      $(vAccum) sum ({$<[_YTD_TY] = {'YTD'}, [Component Group] = {$(vCostComponents)}>}  aggr(sum(Distinct([Actual])), ValueKEY)) $(v/Accum)


      But this results that I am getting two values where the actuals are duplicates and by that I receive a too high figure.


      I have also tried this one;
      $(vAccum) sum({<[Type]={'Cost'}, [Component Group]={$(vCostComponents)},[_YTD_TY]={'YTD'}>} distinct [Actual])$(v/Accum)


      This results in a figure that sums up to being a little too much. Can any of you help / spot what is wrong with my expression?


      Thank you,