Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

paolo_mapelli
Contributor

How to use AGGR function with DUAL

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: How to use AGGR function with DUAL

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?

4 Replies
MVP
MVP

Re: How to use AGGR function with DUAL

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?

balrajahlawat
Esteemed Contributor

Re: How to use AGGR function with DUAL

Go thru this thread:

You will get your answer..

Dual &amp; Exists – Useful Functions

Re: How to use AGGR function with DUAL

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
Contributor

Re: How to use AGGR function with DUAL

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.