8 Replies Latest reply: May 30, 2017 9:02 AM by Marcus Sommer RSS

    Aggr() and multiple <set modifier>s

    Janneke Inia

      Dear all,

       

      I am visualizing a value for each month of current year (including a start value). Usually this works with

       

      =vValueStart +Aggr(RangeSum(Above(total Sum({$<Year={$(=vYear)},Month_nr={'<=12'}>}Value),0,RowNo(total))),Month_nr)

       

      What I want now is to do some calculations, like

       

      =vValueStart

      +Aggr(RangeSum(Above(total Sum({$<Type={1},Year={$(=vYear)},Month_nr={'<=12'}>

      +<Type={2},Year={$(=vYear)},Month_nr={'<=12'}>

      -<Type={3},Year={$(=vYear)},Month_nr={'<=12'}>

      -<Type={4},Year={$(=vYear)},Month_nr={'<=12'}>}Value),0,RowNo(total))),Month_nr)


      However, this calculation does not work. I think it has to do with the combination of Aggr() and multiple <set modifier> but I can't figure out the solution.


      Can you please help me?

       

      Thanks in advance,

       

      Janneke.

        • Re: Aggr() and multiple <set modifier>s
          Marcus Sommer

          I'm not quite sure how you want to combine the different Type values but maybe the following does what you want:

           

          =vValueStart

          +Aggr(RangeSum(Above(total Sum({$<Type={1,2}-{3,4},Year={$(=vYear)},Month_nr={'<=12'}>} Value),0,RowNo(total))),Month_nr)

           

          - Marcus

            • Re: Aggr() and multiple <set modifier>s
              Janneke Inia

              Hi Marcus,

              Thank you for your response, but this is not working for me.

              I have different types of values. Some of them need to be added to the startvalue and some need to be subtracted. It is not only the type that is different. There are multiple dimensions that are included in what I simplified as Type = {1}.

              Do you have another approach?

              Regards, Janneke.

                • Re: Aggr() and multiple <set modifier>s
                  Marcus Sommer

                  Unfortunately it makes it not clearer what do you want to do. If I look on your answer to Sunny I'm not sure that you will need the aggr() and the above(). Periods in the future could be excluded by other ways - in a check that the date is smaller than today() or by using flags in tthe master-calendar or just by checking if certain data exists.

                   

                  I suggest that you try to simplify your approach - beginning just with your inner sum() and splitting your Type-conditions into several expressions, like here simplified:

                   

                  sum({< Type = {1}>} Value) + sum({< Type = {2}>} Value) - sum({< Type = {3}>} Value) ...

                   

                  If those single-parts work like expected you could think of combining them and to shorten/simplify them into lesser expressions.

                   

                  - Marcus

              • Re: Aggr() and multiple <set modifier>s
                Sunny Talwar

                In lay-man words, what are you trying to do here?

                  • Re: Aggr() and multiple <set modifier>s
                    Janneke Inia

                    Hi Sunny,

                    I have different types of values. Some of them need to be added to the startvalue and some need to be subtracted. It is not only the type that is different. There are multiple dimensions that are included in what I simplified as Type = {1}.

                    I want a qraph that shows all the months of the year, but the values can only be shown for the months that are already passed (for now up to May). That is why I have the Aggr and RangeSum around the set analysis.

                    Hope you understand what I would like to achieve here.

                    Greetings, Janneke.