Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Where are you using vEurAmount:?
Can you provide sample apps?
Hi,
You have to use the dollar sign to encapsulte the vEurAmount expression in the aggr formula
Use $(vEurAmount) instead of vEurAmount (
Michael
my bad - of course I used the dollar sign
I'll try to find the time to create a sample app.
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
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
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
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
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
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!