Set Analysis and Variables

[#1]

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

[#2]

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

[#3]

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

[Gysbert_Wassenaar · Accepted solution]

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

[markmccoid]

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.

[#6]

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

Much appreciated.

[#7]

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

[#8]

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

[markmccoid]

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.

[#10]

Hi!

This worked !!!!!!!

Thanks

Tom