topic Re: if with a variable in QlikView

if with a variable

Anonymous — Tue, 14 May 2013 12:30:57 GMT

Hi all,

I have a problem with the following skript:

if ($(Menu) = 1,

sum (Stunden where Reiszeit = 'Nein'), // only Stunden with "Nein"

sum (Stunden)) as Stunden_Projekt // all Stunden

I would like to controll the two different sums per variable.

The problem is that I don't get the right skript for this.

Thank you for helping

Re: if with a variable

Tue, 14 May 2013 12:35:08 GMT

try this

if ($(Menu) = 1 or Reiszeit = 'Nein',Stunden,Stunden) as Stunden_Projekt

Re: if with a variable

Anonymous — Tue, 14 May 2013 12:39:13 GMT

Thank you for your answer!

And what about the sum?

Re: if with a variable

er_mohit — Tue, 14 May 2013 12:40:11 GMT

sum(if ($(Menu) = 1 or Reiszeit = 'Nein',Stunden,Stunden) ) as Stunden_Projekt

hope it helps

Re: if with a variable

Tue, 14 May 2013 12:52:22 GMT

sum(if($(Menu) <> 1 or Reiszeit = 'Nein', Stunden))

Re: if with a variable

Anonymous — Tue, 14 May 2013 13:01:04 GMT

Hi,

there is still a mistake:

Error in expression:

')' expected

Testfile:

LOAD MAID_Z,

left (MAID_Z, 14) as MAID_gesamt_Z,

sum(if( <> 1 or Reiszeit = 'Nein', Stunden)) as Stunden_Projekt

Resident table1

Group by MAID_Z

... and I would like to sum a) Stunden where Reisezeit = "Nein" and menu = 1

b) (else) sum all Stunden also where Reisezeit = "Ja" and "Nein"

=> my idea is to switch the filter of Reizeit "JA" / "Nein" with the variable menu

Hope it helps...

Thank you all for your assistance.

Re: if with a variable

Tue, 14 May 2013 13:04:37 GMT

You forgot to set $(Menu) variable - it's blank in your example, if it possible(a blank) then try this:

sum(if('$(Menu)' <> '1' or Reiszeit = 'Nein', Stunden))

or just set $(Menu) to needed value.

Re: if with a variable

Anonymous — Tue, 14 May 2013 14:48:28 GMT

Sorry, I still don't get it.

Why do you take "or"? The aim is to have a variable that switches the sum-function between all "Stunden" and the "Stunden" which have also Reisezeit = "Nein"

Example:

Nr. Reisezeit Stunden

1 Nein 5

2 Nein 5

3 Ja 5

4 Ja 5

if menu = 1 =>sum Stunden = 20

if menu = 0 => sum Stunden = 10

For setting menu 1 or 0 I have a slider in the design-part to switch.

Hope you can help me.

Re: if with a variable

MayilVahanan — Tue, 14 May 2013 14:54:00 GMT

Try like this

In expression:

= If(menu = 1, Sum(Stunden), Sum({<Reisezeit = {'Nein'}>}Stunden))

Hope it helps

Re: if with a variable

Anonymous — Tue, 14 May 2013 14:59:08 GMT

Thank you,

but there is a mistake in the expression according to qv.

Re: if with a variable

MayilVahanan — Tue, 14 May 2013 15:18:29 GMT

Can u post a sample file?

Re: if with a variable

Anonymous — Tue, 14 May 2013 15:24:31 GMT

Unfortunately no, as this is part of a huge file with to much data.

My aim is to switch the sum-function between all "Stunden" and the "Stunden" which have also Reisezeit = "Nein"

Example:

Nr. Reisezeit Stunden

1 Nein 5

2 Nein 5

3 Ja 5

4 Ja 5

Variable = menu:

menu = 1 =>sum Stunden = 20

menu = 0 => sum Stunden = 10

For setting menu 1 or 0 I have a slider in the design-part to switch.

Doesn't this help?

Thank you for helping

Re: if with a variable

MayilVahanan — Tue, 14 May 2013 16:37:35 GMT

PFA

Regards,

R.Mayil Vahanan

Re: if with a variable

Anonymous — Thu, 16 May 2013 14:20:36 GMT

Hi,

thank u very much for your solution.

How can use this in the script?

I want to use the slider to controll a variable in my script. Is this also possible?

Thank u very much

Re: if with a variable

Anonymous — Fri, 17 May 2013 08:51:23 GMT

How can I use this in the script, as I need the results for further calculations?

Thank u very much!