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

[#1]

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

[MK_QSL]

Where are you using vEurAmount:?

Can you provide sample apps?

[agilos_mla]

Hi,

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

Use $(vEurAmount) instead of vEurAmount (

Michael

[#4]

my bad - of course I used the dollar sign

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

[#5]

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

[#6]

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

[#7]

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

[#8]

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

[#9]

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

[#10]

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!