4 Replies Latest reply: Nov 9, 2015 8:58 AM by PAOLO MAPELLI RSS

    How to use AGGR function with DUAL

    PAOLO MAPELLI

      Good morning,

      I've a straight table with a dimension calculated using DUAL function:

       

      dual(

      if(

        Dimension <= 3,

          '1-3',

          if(

              Dimension <= 7,

              '4-7',

              if(

                  Dimension <= 15,

                  '8-15',

                  if(

                      Dimension <= 21,

                      '16-21',

                      if(

                          Dimension <= 30,

                          '22-30',

                          if(

                              Dimension <= 60,

                              '31-60',

        '60+'

                          )

                      )

                  )

              )

          )

      )

      ,

      if(

        Dimension <= 3,

          1,

          if(

              Dimension <= 7,

              2,

              if(

                  Dimension <= 15,

                  3,

                  if(

                      Dimension <= 21,

                      4,

                      if(

                          Dimension <= 30,

                          5,

                          if(

                              Dimension <= 60,

                              6,

        7

                          )

                      )

                  )

              )

          )

      )

      )

       

      And a measure, of course. The result is visible in attached screenshot.

       

      Now I want to get the MAX value of calculated measures. I've tried using MAX(AGGR(measure,dimension)), with "dimension" like what above, but I have nothing returned, while the expected result would be 5625.I guess that DUAL function can't be used with AGGR.

       

      How can I achieve this ? Any suggestions ?

       

      Thanks in advance.

       

      Paolo.