3 Replies Latest reply on Oct 5, 2018 5:16 AM by Jim Dehner

# dynamic / variable aggregation

is it possible to adapt an aggregation based on the value of another dimension?

For example, I have a dimension 'cumulative' containing the values 'yes' & 'no'.

When the value = 'yes' then I would like a sum(measure x); when the value = 'no', then I would like a last(measure x).

thanks so much for your feedback.

Werner

• ###### 1. Re: dynamic / variable aggregation

good morning

Little confused - you say you have a dimension that is "cumulative" are you saying that it is a calculated field that is aggregated?

if s you have 2 options to further aggregate - one is to use window_sum() in some form or the other involves nested lod's

I prefer the LOD - you would use Fixed on the original calculation that created your yes/no aggregated dimension - then you nest the lod that in a second calculation to get the sum you want-

LOD's create a different virtual level in your data set but are not aggregates in themselves - they therefore can be further aggregated

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: dynamic / variable aggregation

Let me be a bit more precise :-)

in the underneath possible value I translated from Dutch to English. In the attached workbook however all possible values are still in Dutch ... you get the picture :-)

I have a dimension called 'KPI type'. Possible values: descriptive/number/ value ...

I have a dimension called 'cumulative?' which tells me whether I should 'sum' a certain measure or 'last' that same measure. possible values: cumulative/non cumulative.

I have a measure called 'actual (source)' which always contains a numeric value.

I have a dimension called 'actual reached?' which in the case, when I don't have a numeric value in the previous measure, tells me whether the actual is reached or not. Possible values; reached/not reached

I have a calculated measure called 'actual' which contains the following calculation:

if upper([KPI type]) = 'DESCRIPTIEF' then

if upper([actual reached?]) = 'BEHAALD' then 1

elseif upper([actual reached?]) = 'NIET BEHAALD' then 0

end

elseif [cumulatief?] = 'cumulatief' then [actual (source)]

else [actual (source)]

end

Now what I would like to add to this calculation is when the dimension called 'cumulative?' = 'cumulative,''to do a 'sum' and when the dimension called 'cumulative?' = 'non cumulative' to do a 'last' upon the measure 'actual (source'

thanks so much for your feedback.

Werner

• ###### 3. Re: dynamic / variable aggregation

Hi

I'm sorry - I don't have the language skills to work my way through your workbook but maybe the attached will help

your conditional statement it the post is placing a numeric value on each record in the data set - like adding another dimension (column) in the data

that dimension can be treated like any other dimension

see the attached - it is a straight forward superstore example just using category as an analogy to your calculated field

I added this  formula which will summ, take the last table value or set a value of 0 depending on the dimension value

it will return this (calculated down the table Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.