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

    dynamic / variable aggregation

    Werner Engelen

      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
          Jim Dehner

          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
            Werner Engelen

            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
              Jim Dehner

              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.