Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

12 Replies
MK_QSL
MVP
MVP

Where are you using vEurAmount:?

Can you provide sample apps?

agilos_mla
Partner - Creator III
Partner - Creator III

Hi,

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

Use $(vEurAmount) instead of vEurAmount (

Michael

Not applicable
Author

my bad - of course I used the dollar sign

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

pivot_TopX.PNG.png

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!