Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
vinieme12
Esteemed Contributor II

Aggr Level without variable; any other ideas?

Hi

I will try to explain what I am trying to do with a sample attached.

I'm trying to evaluate rank based on either product category or the rank of the Product

Example in the Snapshot I've selected Product D1 and the rank for the same is "6"

but I've achieved that using  a variable to identify the AGGR level

my expression

Expression:  aggr(rank(sum({1}Amount)),$(vAggrLevel))

vAggrLevel  =if(GetSelectedCount(Level2)>0,'Level2','Level1')


Can we do this without using the variable? directly in the expression??

rank_aggr.PNG

Thanks

Vineeth

Tags (2)
1 Solution

Accepted Solutions
manucamon
Valued Contributor III

Re: Aggr Level without variable; any other ideas?

Hi Vineeth,

Try this:

=aggr(rank(sum({1}Amount)),$(='[' &MaxString(

    Aggr(

        If(Upper(Left(Trim(SubField(SubField(GetCurrentSelections(),chr(13)),':',1)),5)) = 'LEVEL',

            Trim(SubField(SubField(GetCurrentSelections(),chr(13)),':',1))

        ,1)

    ,$Field)

)&']'))

Regards!!

14 Replies
MVP & Luminary
MVP & Luminary

Re: Aggr Level without variable; any other ideas?

You could use a $-sign expansion instead of the variable like:

aggr(rank(sum({1}Amount)),$(=if(GetSelectedCount(Level2)>0,'Level2','Level1')))

- Marcus

Re: Aggr Level without variable; any other ideas?

I would just add square brackets in case you have spaces in your field....

Aggr(Rank(Sum({1}Amount)),$(='[' & if(GetSelectedCount(Level2)>0,'Level2','Level1') & ']'))

or directly within the single quotes

Aggr(Rank(Sum({1}Amount)),$(=if(GetSelectedCount(Level2)>0,'[Level2]','[Level1]')))

vinieme12
Esteemed Contributor II

Re: Aggr Level without variable; any other ideas?

Hi Guys,

Apologies; I wasn't clear enough

I meant; I didn't want to evaluate what level was selected separately; But i guess there is no exception

Re: Aggr Level without variable; any other ideas?

I meant; I didn't want to evaluate what level was selected separately; But i guess there is no exception

Can you elaborate on this?

MVP & Luminary
MVP & Luminary

Re: Aggr Level without variable; any other ideas?

Probably not - you need to specify on which dimensions the calculation within the aggr should be performed. Another approach (assumingly not better) could be to use two specialized textboxes and the selections within the Level-fields control their visibility ...

- Marcus

Re: Aggr Level without variable; any other ideas?

try without $- expansion as well

=aggr(rank(sum({1}Amount)),if(GetSelectedCount(Level2)>0,'Level2','Level1'))

vinieme12
Esteemed Contributor II

Re: Aggr Level without variable; any other ideas?

Hi stalwar1 marcus_sommer

I am basically trying to avoid  if(getselectedcount() ...completely, because if the number of level increases I will need to keep on adding to the IF! or that many text boxes

Now I'm trying to see if we could exploit $Field somehow!

Thanks

Vineeth

Re: Aggr Level without variable; any other ideas?

How would the expression know which level to choose from the many you will have? That's the part I am not sure I understand completely?

Re: Aggr Level without variable; any other ideas?

You are back... after ages...