Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

how to sum based on distinct dimension

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

2 Replies

Re: how to sum based on distinct dimension

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?

Not applicable

Re: how to sum based on distinct dimension

Hi Sunny,

The correct figure is supposed to be: 232.709,039749

and I have attached the chart where it is used !

Community Browser