topic Re: How to use variable in IF statement in QlikView

How to use variable in IF statement

musketeers — Tue, 08 Mar 2016 13:49:03 GMT

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

Re: How to use variable in IF statement

sunny_talwar — Tue, 08 Mar 2016 13:49:54 GMT

What about this:

If(country = vCountry, State)

Re: How to use variable in IF statement

pooja_sn — Tue, 08 Mar 2016 14:18:26 GMT

If(country = $(vCountry), State)

Should work. Check value of your variable. Can you share your qvw file..

Re: How to use variable in IF statement

musketeers — Tue, 08 Mar 2016 14:20:49 GMT

Hi Sunny,

Thanks for your help.

But it is not working.

Thanks

Re: How to use variable in IF statement

sunny_talwar — Tue, 08 Mar 2016 14:24:01 GMT

How is vCountry gets its value?

Re: How to use variable in IF statement

martinpohl — Tue, 08 Mar 2016 14:40:21 GMT

Hello,

change it into

If(country = '$(vCountry)', State)

Regards

Re: How to use variable in IF statement

Digvijay_Singh — Tue, 08 Mar 2016 14:42:46 GMT

Hope 'country' spelled correctly

Re: How to use variable in IF statement

musketeers — Wed, 09 Mar 2016 08:20:32 GMT

Hi Sunny, Yes, the variable vCountry is getting value from Country field only. It is dynamic value based on the other filter selection.

Now I want to compare this variable value with the Country Field and get the sum of sales for this country value.

Re: How to use variable in IF statement

musketeers — Wed, 09 Mar 2016 09:01:15 GMT

Hi Martin, when I am using this, I can see that qlikview is replacing '$(vCountry)' with the formula I have written in variable.

Ideally it should calculate the formula and then replace '$(vCountry)' with the result of formula. But this is not happening.

This is the rootcause why this is not working.

Re: How to use variable in IF statement

sunny_talwar — Wed, 09 Mar 2016 09:04:59 GMT

So if you are already making selection in Country field, I would expect this chart to drill down to only show states for the countries you have selected. Why do you need an if statement here?

Re: How to use variable in IF statement

musketeers — Wed, 09 Mar 2016 09:20:55 GMT

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.

Re: How to use variable in IF statement

sunny_talwar — Wed, 09 Mar 2016 09:27:02 GMT

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.

Re: How to use variable in IF statement

avinashelite — Wed, 09 Mar 2016 09:28:02 GMT

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

Re: How to use variable in IF statement

martinpohl — Wed, 09 Mar 2016 09:32:22 GMT

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

Re: How to use variable in IF statement

musketeers — Wed, 09 Mar 2016 09:33:01 GMT

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 .

Re: How to use variable in IF statement

sunny_talwar — Wed, 09 Mar 2016 09:36:42 GMT

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)

Re: How to use variable in IF statement

avinashelite — Wed, 09 Mar 2016 09:37:09 GMT

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

Re: How to use variable in IF statement

amayuresh — Wed, 09 Mar 2016 09:54:52 GMT

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

Re: How to use variable in IF statement

musketeers — Wed, 09 Mar 2016 11:09:46 GMT

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.

Re: How to use variable in IF statement

avinashelite — Wed, 09 Mar 2016 11:17:47 GMT

Cool finally you got the mistake ...