# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
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??

Thanks

Vineeth

Tags (2)
1 Solution

Accepted Solutions
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

## 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

MVP

## 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]')))

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

MVP

## 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

## 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

MVP

## Re: Aggr Level without variable; any other ideas?

try without \$- expansion as well

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

Esteemed Contributor II

## Re: Aggr Level without variable; any other ideas?

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

MVP

## 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?

MVP

## Re: Aggr Level without variable; any other ideas?

You are back... after ages...