Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a variable in qlikview where I am calculating top 1 country.
Now I am using this variable to display the sales of each state for the country from the variable.
So if the variable name is vCountry.
I am using this statement in Dimension of Pie Chat:
If(country = $(vCountry), State)
But thsi is not working.
I need help in resolving this.
Please help me here.
Thanks
Hi Sunny, Thanks for your response.
Please correct me if I am following wrong path.
My requirement is as below.
I have to find the Top 1 country based on the total sales for selected quarter.
Now for that country I need to display total sales State wise.
So my approach is below:
I have created a variable vCountry to get the country name.
Now I am using this variable in dimension of chart to compare with Country field and get the state names.
So when you say get the country name are you using an expression within your variable or is it just a string? If it is an expression, your simple if statement won't work (you might need to use aggr() function). A more efficient alternative would be to use set analysis in that case.
Are you getting the values in the variable ?? To check this add a text object and add your variable here and make the selection . If you getting the value then the expression should work ..
if not check the variable name and column names
if you can see your expression, not the result, a "=" is missing.
Eihter in your variable or in your dynamic dimension
If(country = '$(=vCountry)', State)
Regards
Hi Sunny, It is an expression in variable. I will prefer set analysis but need your help again here. I am not getting how to write set analysis to select the STATE names where country = vCountry .
Lets say your expression is Sum(Sales), can you try this:
Sum({<States = p({<Country = {"=$(vCountry)"}>})>} Sales)
or try this without the variable and see if that works (we can get the variable to work later on)
Sum({<States = p({<Country = {"=ExpressionBehindvCountry"}>})>} Sales)
if you want to do it in set analysis try like this
only({<country = {"=($(=vCountry)"}>}State)
before this
Are you getting the values in the variable ?? To check this add a text object and add your variable here and make the selection . If you getting the value then the expression should work ..
if not check the variable name and column names
There are two options,
1. Use Dimension Limits tab as 1 in Chart and sort Country Descending order by =Sum(Sales)
2. Use expression as =if(rank(sum(Sales))<=1,Country) and Dimension as Country
this will give you the first Country having highest Sales
Hi Avinash,
Thanks for your help.
I realized that I was doing wrong thing. My expression in variable was not working fine. I do not know why. Because when I use that variable in List Box, I was getting one value. But when I used that variable in Text Box, It was not showing any result.
Now I am using the below expression in my variable:
only(if(aggr(Rank(Sum([Sales Amt])),Country)=1,Country))
and I am getting country name in Text Box and List Box both. (THIS IS FOR TESTING THAT MY VARIABLE IS WORKING FINE)
Now I am using this variable as Sunny has suggested and am getting the result.
I am very thankful to all for your valuable time and suggestion.
Cool finally you got the mistake ...