Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Would you be able to share a sample where we can see the issue and also, provide the numerical output of what you think should be the correct answer?
Hi Sunny,
The correct figure is supposed to be: 232.709,039749
and I have attached the chart where it is used !