topic Re: Aggr Level without variable; any other ideas? in QlikView

Aggr Level without variable; any other ideas?

vinieme12 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggr Level without variable; any other ideas?

marcus_sommer — Tue, 24 Jan 2017 11:43:32 GMT

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?

sunny_talwar — Tue, 24 Jan 2017 12:12:46 GMT

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

Re: Aggr Level without variable; any other ideas?

vinieme12 — Tue, 24 Jan 2017 12:27:13 GMT

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?

sunny_talwar — Tue, 24 Jan 2017 12:28:41 GMT

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?

Re: Aggr Level without variable; any other ideas?

marcus_sommer — Tue, 24 Jan 2017 12:35:32 GMT

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?

Kushal_Chawda — Tue, 24 Jan 2017 12:38:53 GMT

try without $- expansion as well

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

Re: Aggr Level without variable; any other ideas?

vinieme12 — Tue, 24 Jan 2017 12:40:39 GMT

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?

sunny_talwar — Tue, 24 Jan 2017 12:46:20 GMT

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?

sunny_talwar — Tue, 24 Jan 2017 12:47:48 GMT

You are back... after ages...

Re: Aggr Level without variable; any other ideas?

marcus_sommer — Tue, 24 Jan 2017 13:01:02 GMT

I doubt that $Field could be useful here because you will furthermore need any checkings if there is any selection anywhere. The if-loop itself might be replaceable with a pick(match()) and also there might be ways to create such a expression-statement with a kind of string-loop. But I think it won't make your task easier - rather the opposite will happens.

Why not just copying the if-checking within a variable ten times beneath eachother ... ok. it won't look very nice but there won't be a real performance-impact on this textbox-expression.

- Marcus

Re: Aggr Level without variable; any other ideas?

Kushal_Chawda — Tue, 24 Jan 2017 13:16:40 GMT

Yes, busy with lot of work.

Re: Aggr Level without variable; any other ideas?

Anonymous — Tue, 24 Jan 2017 13:54:05 GMT

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

Re: Aggr Level without variable; any other ideas?

vinieme12 — Tue, 24 Jan 2017 14:04:46 GMT

Thanks Manuel, this is exactly what I was trying to do but couldn't

Cheers

Vineeth

Re: Aggr Level without variable; any other ideas?

Anonymous — Tue, 24 Jan 2017 14:07:49 GMT

You're wellcome!!

Cheers!