topic Re: aggr with variable dimension issue in QlikView

aggr with variable dimension issue

kkkumar82 — Tue, 28 Jul 2015 10:03:13 GMT

Hi all,

I have a expression as follows

if(aggr(Rank(Sum({1} Spend)),Supplier)<=20, expression & 'your in top 20', expression)

The above expression is working fine and displaying the expression with message if the selected supplier is in top 20.

But I have to write the similar kind of expressions for Manufacturer , Product, Commodity.

so I created a variable like this

vCPSelected = if(GetFieldSelections(Commodity)>0,'Commodity',if(GetFieldSelections(Product)>0,'Product',if(GetFieldSelections(Manufacturer)>0,'Manufacturer','Supplier')))

and I replaced the aggr dimension with the above variable

if(aggr(Rank(Sum({1} Spend)),$(vCPSelected))<=20, expression & 'your in top 20', expression) but when I selected a supplier (for eg) in top 20 , I am unable to get the first expression in "if" condition.

along with this I have also a line saying

<selected supplier> spend is expression.

Note: All these I am using in a text box

Thanks

Kiran Kumar

Re: aggr with variable dimension issue

marcus_sommer — Tue, 28 Jul 2015 10:22:34 GMT

If your variable started with "=" it should work. That meant the variable will be evaluated and the result will be used with your aggr-expression and without an equal sign only the expression as string will be applied - in such cases could you use $(='$(vCPSelected)').

Further getfieldselections returned a string of the selected values - if you want to count if there is any selection you need getselectedcount().

- Marcus

Re: aggr with variable dimension issue

sunny_talwar — Tue, 28 Jul 2015 10:28:04 GMT

You are saying that this is working -> if(aggr(Rank(Sum({1} Spend)),Supplier)<=20, expression & 'your in top 20', expression)

but this isn't? -> if(aggr(Rank(Sum({1} Spend)),$(vCPSelected))<=20, expression & 'your in top 20', expression)

Can you check if your variable $(vCPSelected) shows 'Supplier' when you have one selected?

Re: aggr with variable dimension issue

kkkumar82 — Tue, 28 Jul 2015 10:29:45 GMT

Thanks Marcus , I used getselectedcount() but in a hurry I typed getcurrentselections.

I have got the solution in the same way as u suggested but I have used like as

$(=$(vCPSelected)) and to show the for eg Supplier is in top 20 , I have used $(vCPSelected).

By using "=" aggr is considering the value of the variable as dimension where as $(vCPSelected) is just returning a string

which aggr is not treating as dimension.

Thanks

Kiran Kumar

Re: aggr with variable dimension issue

perumal_41 — Tue, 28 Jul 2015 10:33:26 GMT

Hi Kiran,

Try like below

Sum(aggr(if(Rank(Sum({1} Spend))<=20, expression & 'your in top 20', expression),$(vCPSelected)))