topic Re: Set Analysis and Variables in QlikView

Set Analysis and Variables

Thu, 07 Nov 2013 15:20:11 GMT

Hi,

Im using the following variable vSubSite :

if([Site]='North Division','Sub Site 1',

if([Site]='Central Division','Sub Site 2',

if([Site]='South Division','Sub Site 3',)))

which works (I'm testing this in a Text Box).

I have a chart with the following set analysis :

=sum({ <sub_site = {'Sub Site 1'}>} Number)

which plots perfectly.

If I try and use vSubSite1 : =sum({ <sub_site = {$(vSubSite1)}>} Number) the chart fails to plot.

Is there something I'm doing wrong?

Many thanks for your time.

Tom

Re: Set Analysis and Variables

Thu, 07 Nov 2013 15:25:20 GMT

Hi,

It is due to the space contained in your variable content, which is = Sub Site 1

Insert the variable name between brackets [], sth like {[$(vsubsite1)]}

See the doc I have written: http://community.qlik.com/docs/DOC-4951

Fabrice

Re: Set Analysis and Variables

sivarajs — Thu, 07 Nov 2013 15:29:25 GMT

Try this

sum({ <sub_site = {$(=vSubSite1)}>} Number)

sum({ <sub_site = {=$(=vSubSite1)}>} Number)

Re: Set Analysis and Variables

israrkhan — Thu, 07 Nov 2013 15:37:24 GMT

what is value is vSubSite1 in right side.

you are assigning to same variable, that using right side in set..

try like below in variable expression,

vSubSite1 =sum({ <sub_site = {$(vSubSite1)}>} Number)

if vSubSite1 in null, then it will not show any value...

and use this in text box

sum({ <sub_site = {$(vSubSite1)}>} Number)

Re: Set Analysis and Variables

Clever_Anjos — Thu, 07 Nov 2013 15:40:35 GMT

sum({ <sub_site = {"=$(=vSubSite1)"}>} Number)

should work

Re: Set Analysis and Variables

Thu, 07 Nov 2013 15:44:15 GMT

Hi Fabrice!

Thanks for your reply. This works if I hard code the variable vsubsite1 = Sub Site 1

If I try and use the expression

vsubsite1 =

if([Site]='North Division','Sub Site 1',

if([Site]='Central Division','Sub Site 2',

if([Site]='South Division','Sub Site 3',)))

then the chart doesn't plot.

Any ideas?

Tom

Re: Set Analysis and Variables

Thu, 07 Nov 2013 15:47:18 GMT

put the variable name between ' '

Cheers

Juan Pedro

Re: Set Analysis and Variables

Thu, 07 Nov 2013 15:58:38 GMT

Hi,

Thanks, but none of these solutions seem to work.

Cheers,

Tom

Re: Set Analysis and Variables

Thu, 07 Nov 2013 16:01:18 GMT

Hi, thanks but this solution doesn't work either (ive tried both " and ' )

Thanks,

Tom

Re: Set Analysis and Variables

Thu, 07 Nov 2013 16:42:16 GMT

Tom,

vEssai is a variable

= if(TIME_Key < 15, 'GAMME GOURMANDE')

As you see, there is a test (with if statement based on TIME that is chosen in a box), and the value is GAMME GOURMANDE (with a space)

sum ({<CONTENANT_LName = {[$(vEssai)]} > } [Ventes Valeur])

where

[Ventes valeur] is my variable

CONTENANT_LName my diimension

vEssai is enclosed into brackets []

So it should work.

Perhaps, it does not work in your graph because there are several Site, and the formula does not return anything

Fabrice

Re: Re: Set Analysis and Variables

Fri, 08 Nov 2013 11:50:03 GMT

Hi Fabrice,

Thanks again for your reply - it is very much appreciated!!

I've attached a sample document of what I am trying to achieve. I need to show a chart for each Sub Site depending on which Main Site has been selected.

I would be very grateful if you could take a look and see where I

am going wrong. I've tried adding the variable to the expressions in Chart 1 but no data is being shown,

Many thanks again for your time.

Tom

Re: Set Analysis and Variables

Fri, 08 Nov 2013 11:59:20 GMT

Tom,

Because I have the Personal Edition, I will not be able to open them.

But you have to keep in mind that the set analyses are computed once per chart, not once per row. So, if the result of the set is different for each row, it will NOT work. In that case, you need to update the model to relate YourRowDimOfTheChart with the Sub types you want to compute.

Fabrice

Re: Re: Re: Set Analysis and Variables

Gysbert_Wassenaar — Fri, 08 Nov 2013 15:56:15 GMT

Put an = in front of the expressions in the variables and surround the variable expansions with single quotes. See attached qvw.

Re: Set Analysis and Variables

markmccoid — Fri, 08 Nov 2013 16:17:56 GMT

Here is how I would do it:

To test this and see if it is evaluating as you would expect, change your chart type to a straight table and take out the label on the expression. Then you can hover over the label and it will show you what the expression looks like with the variable expanded.

sum({<[TYPE]={'Type A'},SUB_SITE = {$(=$(vMainSite1))} > }TOTAL)

Expanded view.

Re: Set Analysis and Variables

Fri, 08 Nov 2013 16:39:11 GMT

Thanks to everyone who took the time to answer this post.

Much appreciated.

Re: Set Analysis and Variables

Mon, 11 Nov 2013 11:43:08 GMT

Hi,

Something strange is happening when I copy your working example into my main document. I replaced the sample data in your document with actual data and field names and it worked perfectly.

When I copy this over to my actual document the charts stop working all together. I'm not sure if something is wrong with my local settings as the chart title only works if I remove the ' ' quotes from around the variable :

=$(vAdmTrendSite1)

as opposed to

='$(vMainSite1)'

Any ideas?

Tom

Re: Set Analysis and Variables

Mon, 11 Nov 2013 16:17:31 GMT

Hi,

I Have tried your suggestion. It is showing the whole 'If' statement that is creating the variable rather than the variable value itself.

Any help is appreciated!

Tom

Re: Set Analysis and Variables

markmccoid — Mon, 11 Nov 2013 17:30:24 GMT

Make sure you have an = sign in the variable itself in front of the if statement. That should cause it to be evaluated before being used in the Set Analysis.

Re: Set Analysis and Variables

Wed, 13 Nov 2013 15:25:01 GMT

Hi!

This worked !!!!!!!

Thanks

Tom