How to use variable in IF statement

[musketeers]

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

[musketeers]

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.

[sunny_talwar]

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.

[avinashelite]

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

[martinpohl]

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

[musketeers]

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 .

[sunny_talwar]

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)

[avinashelite]

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

[amayuresh]

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

[musketeers · Accepted solution]

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.

[avinashelite]

Cool finally you got the mistake ...