topic Re: Limit Pivot Table - Top 10 (with variable expression in aggr) in QlikView

Limit Pivot Table - Top 10 (with variable expression in aggr)

Tue, 18 Feb 2014 10:39:50 GMT

Hello community,

I'm trying to integrate a top X pivot table which only shows the top amounts based on the variable set for the limit.

I know I have to use a calculated dimension with aggr and rank - similar to this one:

=IF (aggr(rank( sum(Quantity) ,0) , Productname )<= 5 , Productname )

in my case, "Quantity" is a variable for Amount that looks like this:

vEurAmount:

=if(NOT Wildmatch(InvoiceCurrency,'EUR'), (Amount / (1 * ECBrate)),Amount)

So when I insert vEurAmount I get an: "Error: Garbage after expression: "EUR" "

How can I solve this issue?

Thank you,

Thorsten

Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

MK_QSL — Tue, 18 Feb 2014 10:41:47 GMT

Where are you using vEurAmount:?

Can you provide sample apps?

Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

agilos_mla — Tue, 18 Feb 2014 10:43:44 GMT

Hi,

You have to use the dollar sign to encapsulte the vEurAmount expression in the aggr formula

Use $(vEurAmount) instead of vEurAmount (

Michael

Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

Tue, 18 Feb 2014 12:57:55 GMT

my bad - of course I used the dollar sign

I'll try to find the time to create a sample app.

Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

Tue, 18 Feb 2014 13:56:43 GMT

WildMatch is an integer function

perhaps remove the NOT and inverse the if statement

if(Wildmatch(InvoiceCurrency,'EUR') ,Amount, (Amount / (1 * ECBrate)) )

will work because WildMatch will return 1 for Euros that will be considered as true.

No = sign in the definition if you loop over fields

Fabrice

Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

Thu, 20 Feb 2014 08:04:21 GMT

Hello Fabrice,

it gave me the same error but now I just inserted the if-stmt instead of the variable and this seems to work. Since I only need to use it in one pivot table I'm fine with it.

But now another problem came up. 🙂

Say I have two dimensions in the pivot:

Department and the calculated topX dimension like this:

=IF (aggr(rank( sum(if(NOT Wildmatch(InvoiceCurrency,'EUR'), (Amount / (1 * ECBrate)),Amount)) ,0) , Product)<= $(vTopX) ,Product)

And my expression like this:

SUM($(vEurAmount))

I was expecting that this would list the top X products for each department, instead only the overall top X are shown.

What do I need to do to change this?

Actually I was hoping to use a cyclic group to replace product with other fields like this:

=GetCurrentField([CyclicGroup]) but this this throws: "Error in calculated dimension".

Hope you have an idea how to fix this.

Thank you,

Thorsten

Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

Thu, 20 Feb 2014 08:32:03 GMT

Thorsten,

I am surprised by your result with 'EUR'. Did you use single quotes ?

=IF(aggr ....)

Remove the the equal sign if any. If = sign, it will be ONE result. If no = sign, it will be an expression to be interpreted. And you can do : SUM($(vEurAmount))

In your equation, you may use also parameters: $1, $2 .... When using the variable, you pass the parameters between parenthesis (), sth like:

SUM($(vEurAmount('Product')) or sth returning Product

Please try:

IF (aggr(rank( sum(if(NOT Wildmatch(InvoiceCurrency,'EUR'), (Amount / (1 * ECBrate)),Amount)) ,0) , $1)<= $(vTopX) ,$1)

And the call:

SUM($(vEurAmount(GetCurrentField([CyclicGroup])))

or SUM($(vEurAmount($(=GetCurrentField([CyclicGroup])))) (I have not QV today, I cannot test)

Fabrice

Re: Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

Thu, 20 Feb 2014 12:01:57 GMT

Fabrice,

ok, so I have tried various ways but couldn't get it to work.

Could you show me how to do it based on this sample I downloaded a few days ago?

Since this seems simpler I already gave it try but got no values out of it.

Thank you,

Thorsten

Re: Re: Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

Thu, 20 Feb 2014 21:23:03 GMT

Thorsten,

I have the Personal Edition: I will not be able to open your qvw.

I have created 3 variables:

vTop : 2 (an integer)

vDim2 : IF (aggr(rank(sum(if(NOT Wildmatch(InvoiceCurrency,'EUR'), (Amount * ECBrate),Amount)),0) , $1)<= $(vTop) ,$1)

Calculated Dimension: =$(vDim2(Product))

I have created two extra variables to store the measure names: Amount and Amount * ECBrate. It is still OK.

However, that retruns me the first 2 products (all departments): because there is also Department dimension in the chart, I have the same 2 products even if there are not the top 2 for this department.

I join the test I have made. If I cannot use your application, you can use mine.

Fabrice

Re: Re: Re: Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

Fri, 21 Feb 2014 08:33:16 GMT

Fabrice thanks alot for the effort you put into this issue.

So do I understand you correctly, that with the Department dimension added to the pivot, I can't get the top products for each department but only overall?

I've played around with your qvw and added a third department and changed the product numbers to letters.

See the screenshot.

Here's what I noticed - that confirms what you wrote above:

Dept. 3 has a X product with 999 as amount. When I change this amount to 1 or lower than the rest of the products - Dept. 3 is gone. Also the vTop variable has an effect on the products but not the Dept. dimension. So vTop=5 will show the top 5 products overall but won't list the top products for each Dept.

Hmmm... so I believe this won't work the way I expected. I will have a look through the demo apps, maybe I can find a solution there.

Ideas are still welcome though

Thank you again!

Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

Fri, 21 Feb 2014 08:39:13 GMT

Thorsten,

See a doc by Oleg Troyansky : http://community.qlik.com/docs/DOC-5187

I have seen it but not time to read/understand it

It can give you clue for what you want to do

Fabrice

Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

Mon, 14 Apr 2014 15:34:07 GMT

Hello,

I know that this topic is solved and old, but for others who come here, i will show you how i did solve my problem with this error:

i just surrounded my variable with chr(39), the ASCII code for a '

eg: $(=Chr(39) & GetFieldSelections(ENSEIGNE_MAGASIN) & Chr(39))

Then Qlikview understand that it is a character string.

Re: Limit Pivot Table - Top 10 (with variable expression in aggr)

Fri, 30 May 2014 22:17:43 GMT

Good evening friends

I am facing a problem and I need your help

I try to display the first 10 sales per country in a Pivot table

so I added this expression in dimensions

and as a result I have

as you can see for 2010 it gives a result USA-> France-> germany and the logical order and it

USA-> France-> UK why?

Thank you for your answers