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,
Stefan
