topic Re: Set analysis of a variable containing functions with other set analysis in QlikView

Set analysis of a variable containing functions with other set analysis

Anonymous — Tue, 15 Apr 2014 07:33:08 GMT

Hi,

My first community post. Hope you can help me. I've received enourmous help from here merely by Googling. Great community!

My problem

I want to create a set analysis of a variable containing functions that includes other set analysis.

Example

I have a defined variable: that is vMySpecialMargin = sum({$<Year={2014}>} Sales) / sum({$<Year={2010}>} Costs)

I want to do this in a chart expression: ={$<BusinessUnit={SecretServices}>} $(vMySpecialMargin)

That gives me nothing. It doesn't work.

What I've already tried in the chart expression that doesn't work

={$<BusinessUnit={SecretServices}>} $(vMySpecialMargin)

=Sum({$<BusinessUnit={SecretServices}>} $(vMySpecialMargin)) //The sum would be redundant, but i thought it might've helped with the set analysis.

Is it possible to do this?

I use the function in the variable on a lot of places and when I need to tweek it it takes a lot of time as it is now. If I could put it in one common place for the charts and just change a customize a few set analysis code snippets once in each chart it would save me a lot of time and effort.

Of course my example is for silly data but it explains my problem.

Kind regards

Message was edited by: Rasmus Toivonen

Re: Set analysis of a variable containing functions with other set analysis

alexandros17 — Tue, 15 Apr 2014 07:37:03 GMT

This is what you need:

sum({$<Year={2014}, BusinessUnit={'SecretServices'}>} Sales) / sum({$<Year={2010}, BusinessUnit={'SecretServices'}>} Costs)

Let me know

Re: Set analysis of a variable containing functions with other set analysis

Anonymous — Tue, 15 Apr 2014 07:49:11 GMT

Thanks for the super quick answer!

But... that is exactly the function I have in my expression now (but much more complex and with more set analysises.).

I wanted to lift out parts of the function into a variable and just change a part of the set analysis for each time I re-use the function/expression that I've moved to the variable.

I want to do this:

Chart 1: ={$<BusinessUnit={SecretServices}>} $(vMySpecialMarginFunction)

Chart 2: ={$<BusinessUnit={NavySeal}>} $(vMySpecialMarginFunction)

Chart 3: ={$<BusinessUnit={McRonald}>} $(vMySpecialMarginFunction)

where $(vMySpecialMarginFunction) also has its own set analysis in its definition.

Re: Set analysis of a variable containing functions with other set analysis

alexandros17 — Tue, 15 Apr 2014 07:52:48 GMT

Unfortunately the syntax is wrong, you have to insert the condition for BusinessUnit in $(vMySpecialMarginFunction)

Re: Set analysis of a variable containing functions with other set analysis

Anonymous — Tue, 15 Apr 2014 07:59:54 GMT

So basically it is not possible to do this?

Re: Set analysis of a variable containing functions with other set analysis

alexandros17 — Tue, 15 Apr 2014 08:03:48 GMT

It is possible but not in that way

With the sysntax I gav eyou try to substitute the businessUnit section with a variable ...

sum({$<Year={2014}, BusinessUnit={$(varValue)}>} Sales) / sum({$<Year={2010}, BusinessUnit={{$(varValue)}}>} Costs)

Re: Set analysis of a variable containing functions with other set analysis

tresB — Tue, 15 Apr 2014 08:06:55 GMT

I don't like 'not possible'. You can do that with multiple variables. Like:

Var1 ='sum({$<Year={2014}'

Var2 ='>} Sales)'

Var3 =' / sum({$<Year={2010}'

Var4 ='>} Costs)'

Now your expressions would be like:

=$(Var1) & ' ,BusinessUnit={SecretServices}' & $(Var2) & $(Var3) & ' ,BusinessUnit={SecretServices}' & $(Var4)

Note: Be careful of the commas.

and similarly for other expressions.

Re: Set analysis of a variable containing functions with other set analysis

Anonymous — Tue, 15 Apr 2014 08:49:10 GMT

Thank you for this!

Not exactly a "graceful" solution. But still a solution.

I understand that it this wasn't solveable in a way as nice as I would've liked.