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.

[swuehl · Accepted solution]

It's not about the dual() function, you can't use a calculated dimension as an aggr() dimension.

Any chance to create your classification in the script?

[Anonymous]

Go thru this thread:

You will get your answer..

Dual &amp; Exists – Useful Functions

[MK_QSL]

Use something like below

If(Dimension <= 3,Dual('1-3',1),

    if(Dimension <= 7,Dual('4-7',2),

    if(Dimension <= 15,Dual('8-15',3),

    if(Dimension <= 21,Dual('16-21',4),

    if(Dimension <= 30,Dual('22-30',5),

    if(Dimension <= 60,Dual('31-60',6),Dual('60+',7)))))))

[paolo_mapelli]

Hi,

I've put the classification in the script, as you suggested. Applied to my dashboard's architecture, strengths of this solution are greater than weaknesses.

Thank you.

Paolo.