topic Re: if .. then... variables .... please help! in QlikView

if .. then... variables .... please help!

Mon, 31 Oct 2011 16:32:11 GMT

Hi everybody,

take a look at:

This does the following:

Is there a way to do it easyer?

example:

var SB = sum("sb")

var SKY = sum(Sky)

if SB > SKY then SB else SKY

???

I'm asking because I have to integrate many other items like SB or SKY in the formula and it would get very complex.

I think I can't use a QLIKVIEW VARIABLE ("set" or "let") because this is a formula based on a diagram chart with different dimensions...!)

Any idea?

Regards,

jup

if .. then... variables .... please help!

Mon, 31 Oct 2011 16:53:46 GMT

Go to variable editor and create:

vSB: sum(HEL_PLACEMENT_={'728 X 90'}) HEL_DATE_IMP)

similarly vSky. In the chart expression would be: if($(vSB)>$(vSky),'SB','SKY')

Since, there is no equals sign for variable expression it will be computed dynamically.

PS: The field names are not correct in my code, please use appropiate ones.

KIran.

Re: if .. then... variables .... please help!

Tue, 01 Nov 2011 11:01:26 GMT

Hi Kiran,

I added a new variable "vSB" to the variable editor. This is the formula:

But I get the expected result...: I'm using a

pivot chart based on a daily calculation but the variable's calculation is not based on the daily DIMENSION, it's global 😕

green = expected value (daily)

red = variable's calculation (vSB)

Any idea?

regards,

jup

Re: if .. then... variables .... please help!

Tue, 01 Nov 2011 11:19:36 GMT

Remove "=" sign in the variable and in the chart make the expression as =$(vSB)

Kiran.

Re: if .. then... variables .... please help!

Tue, 01 Nov 2011 12:06:43 GMT

You did it! 🙂

Great job!

Many thanks!!!

Re: if .. then... variables .... please help!

Tue, 01 Nov 2011 13:43:59 GMT

Hi Kiran,

maybe you could help me with the next thing

Now, take a look at my (your) formula:

It works great

I have to integrate some other items to check, not only vSB and vSKY.

I have to add vMREC, vPOP, vINTER, vSTICKY ... to the formula.

But if I will only add "MREC", my formula will grow and grow and get more complex:

ALso my the application speed will slow down (to calculate the object)

Do you have any idea to create a shorter (easyer and faster) formula...?

thx again,

jup

Re: if .. then... variables .... please help!

Tue, 01 Nov 2011 13:59:17 GMT

Hi Jup,

Good that it worked..!

You are right, more memory will be consumed. I have an alternative that can work in straight table (for Pivot Table you need QV 11 ).

Create a straight table with expressions, one each for a variable. Label the expressions as ValSB, ValSKY etc... This means that you computed each of these expressions for the dimensions. Create additional expressions in the same table but using the expression lables instead of the variables as :

If(ValSB>ValSKY,ValSB,ValSKY) etc.. This would decrease the calculation time. Now you can go to presentation tab and hide these columns ValSB, ValSKY etc..

To contrast the ways:

In the former the expression is computed for each of the dimensions everytime variable is called ie., In your current expression, value of SB is calculated twice if the condition is true. In the latter expression is only computed once for the first time and used later on.

I know this wont work for Pivot table as you cannot hide the expressions (which is there in QV11) but this is the best Idea I have as of now. Let me know if you have a better alternative.

Regards,

Kiran.