topic Re: Nested if statement within set analysis in QlikView

Nested if statement within set analysis

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I have a chart expression using set analysis, part of which is as follows:

Phase={'$(vPhase)'}

The variable 'vPhase' is changed when the user selects a button - Early, Late, All

The problem is, whilst 'Early' and 'Late' are values in field 'Phase' (so expression works fine for those two), 'All' is not a value in 'Phase' - it's just all values!

I was thinking I would replace the above part of my set analysis to include an if statement, but can't get it working. So far, I have:

Phase={'$(=IF(vPhase='Early','Early',IF(vPhase='Late','Late',IF(vPhase='All','???')))'}

But, I'm not sure if I'm declaring my variables correctly here - never sure when they should/shouldn't be bracketed - and I also don't know what I want to say for the 'then' part of my 'If' statement for 'All'. Can I say IF(vPhase='All','Early' and 'Late) ?

Re: Nested if statement within set analysis

trdandamudi — Fri, 23 Sep 2016 08:19:58 GMT

May be as below:

Phase={'$(=IF(vPhase='Early','Early',IF(vPhase='Late','Late',IF(vPhase='All','*')))'}

Re: Nested if statement within set analysis

Anonymous — Fri, 23 Sep 2016 08:22:55 GMT

Hi Jessica,

Try this:

If(vPhase = 'All',

Use this set analysis: Phase={'*'},

Phase={'$(vPhase)'}

)

Regards!!

Re: Nested if statement within set analysis

jessica_webb — Fri, 23 Sep 2016 08:24:43 GMT

Hi,

Unfortunately that doesn't seem to work - I just get the red 'error' line under that part.

Re: Nested if statement within set analysis

anushree1 — Fri, 23 Sep 2016 08:24:50 GMT

Could you share a sample of your application

Re: Nested if statement within set analysis

jessica_webb — Fri, 23 Sep 2016 08:28:24 GMT

Hi Manuel,

I don't understand how that would fit into my overall expression.

In its entirety it reads:

=IF (Name='$(vName)', COUNT ({1<Overall ={4},Phase={'$(vPhase)'}, [Date set]={1} >}

DISTINCT ID)/COUNT (

{1<Overall={1,2,3,4}, Phase={'$(vPhase)'}, [Date set]={1} >}

DISTINCT ID),

COUNT (TOTAL {1<Overall={4},Phase={'$(vPhase)'}, [Date set]={1} >}

DISTINCT ID)/COUNT ( TOTAL

{1<Overall={1,2,3,4}, Phase={'$(vPhase)'}, [Date set]={1} >}

DISTINCT ID))

How would I add the new If statement in place of the bolded parts?

Thanks,

Jess

Re: Nested if statement within set analysis

sunny_talwar — Fri, 23 Sep 2016 08:32:53 GMT

How about this:

Phase={"$(=Pick(Match(vPhase, 'Early', 'Late', 'All'), 'Early', 'Late', '*'))"}

Re: Nested if statement within set analysis

jessica_webb — Fri, 23 Sep 2016 08:37:37 GMT

Absolutely perfect!

Thank you so much (for the hundredth time this week!).

Best wishes,

Jess

Re: Nested if statement within set analysis

tamilarasu — Fri, 23 Sep 2016 08:42:38 GMT

Sunny has already suggested a best solution. Just thought to share suing If method.

Phase={'$(=IF(vPhase ='Early','Early',IF(vPhase='Late','Late','*')))'}

Ignore the red squiggly lines.

Re: Nested if statement within set analysis

jessica_webb — Fri, 23 Sep 2016 08:45:52 GMT

Hi Tamil,

Thank you - that's really useful!

One of the issues I was having was declaring my variables properly, so this will be a big help for next time!

Thanks again,

Jess

Re: Nested if statement within set analysis

sunny_talwar — Fri, 23 Sep 2016 08:49:57 GMT

I think you accidentally marked your own response as helpful when you might be wanting to mark Tamil's response as helpful

Re: Nested if statement within set analysis

sunny_talwar — Fri, 23 Sep 2016 08:52:17 GMT

I think the red squiggly can be avoided by using double quotes around the expression:

Phase={"$(=If(vPhase ='Early','Early',If(vPhase='Late','Late','*')))"}

Re: Nested if statement within set analysis

tamilarasu — Fri, 23 Sep 2016 09:05:19 GMT

You are right Sunny. I thought of editing my post but you have already suggested a good solution. So I just left it, as it is. BTW, I really don't bother about the points. Rather, I am very much concerned, if my solution helped them in someway.

Re: Nested if statement within set analysis

jessica_webb — Fri, 23 Sep 2016 09:08:29 GMT

Haha, thank you!

I got my email notification and thought "that's nice, but I don't really understand what I said that was helpful.."

Oh dear, Friday fatigue!

Re: Nested if statement within set analysis

sunny_talwar — Fri, 23 Sep 2016 09:11:53 GMT

Good

But my suggestion was not to get points to you my friend. These responses are going to be used by others in the future and I would want them to look at responses which were helpful. In this case Jessica mentioned that your response was helpful, so other should see them as well.

Best,

Sunny

Re: Nested if statement within set analysis

Anonymous — Fri, 23 Sep 2016 09:23:09 GMT

Hi Jessica, I didn't know all expression was written.

Other option than Sunny solution is modify vPhase value on button, on All button, or when option is All, write on variable *.

Regards!

Re: Nested if statement within set analysis

jessica_webb — Fri, 23 Sep 2016 09:27:17 GMT

Hi Manuel,

Another really good suggestion, thank you.

Best wishes,

Jess

Re: Nested if statement within set analysis

Anonymous — Fri, 23 Sep 2016 09:30:57 GMT

You're wellcome!

Re: Nested if statement within set analysis

kmstephenson — Fri, 06 Oct 2017 14:58:45 GMT

Does this functionality not work in newer versions of QlikView 12? I used this syntax successfully in an older version of the tool but just noticed that it is not resolving correctly anymore. Only thing I can think of was upgrade to newer release of QlikView.

Re: Nested if statement within set analysis

sunny_talwar — Fri, 06 Oct 2017 15:14:09 GMT

There might have been some changes in the most recent version of QlikView.... check here:

Quotes in Set Analysis‌