topic Re: Using IF with a variable in expression in QlikView

Using IF with a variable in expression

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

I am trying to use an if statement with a variable in my expression and I get no results. The variable works when I use the variable on it's own but when used with the if I get no results.

I have tried:

if(OrderQtr='Apr-Jun 2018',$(vAvgOrderCost),0)

if(OrderQtr='Apr-Jun 2018',sum($(vAvgOrderCost)),0)

sum($(vAvgOrderCost)if(OrderQtr='Apr-Jun 2018',0))

Nothing seems to work. Thanks

Re: Using IF with a variable in expression

YoussefBelloum — Fri, 14 Sep 2018 12:39:59 GMT

Hi,

what is the content of the variable ?

Re: Using IF with a variable in expression

rubenmarin — Fri, 14 Sep 2018 12:43:45 GMT

Hi, and also.."if(OrderQtr='Apr-Jun 2018',1,0)" returns '1' in the expected quarter?

Re: Using IF with a variable in expression

ashokunbi — Fri, 14 Sep 2018 13:39:03 GMT

The variable content is =Sum([Unit Cost PO])/Count([Unit Cost PO])

Also if(OrderQtr='Apr-Jun 2018',1,0)" did not return 1 in the expected quarter

Thanks

Re: Using IF with a variable in expression

ashokunbi — Fri, 14 Sep 2018 13:40:15 GMT

The variable content is =Sum([Unit Cost PO])/Count([Unit Cost PO])

Thanks

Re: Using IF with a variable in expression

dplr-rn — Fri, 14 Sep 2018 13:41:17 GMT

what is the column type of OrderQtr. that maybe the issue there

Re: Using IF with a variable in expression

ashokunbi — Fri, 14 Sep 2018 13:48:37 GMT

Money

Thanks

Re: Using IF with a variable in expression

dplr-rn — Fri, 14 Sep 2018 13:51:25 GMT

not sure why OrderQuarter is money but try converting it to string before doing the compare

Re: Using IF with a variable in expression

YoussefBelloum — Fri, 14 Sep 2018 13:57:10 GMT

try to remove the = sign on the variable and see if the expressions works

Re: Using IF with a variable in expression

ashokunbi — Fri, 14 Sep 2018 14:02:18 GMT

I'm sorry OrderQtr is not money. It is text. The result of the expression is money.

Re: Using IF with a variable in expression

ashokunbi — Fri, 14 Sep 2018 14:05:40 GMT

I did. Didn't work.

Re: Using IF with a variable in expression

rubenmarin — Sat, 15 Sep 2018 10:21:00 GMT

Hi Ade, then your main problem is the comparison, "Apr-Jun 2018" seems created as quartername, maybe with:

If(quartername(OrderQtr)='Apr-Jun 2018',$(vAvgOrderCost),0)

Or try showing OrderQtr in an expression to check the value, then copy and paste in the expression.

Another option is using a number field to avoid formatting issues:

Num(QuarterStart(OrderQtr)) as NumOrderQtr // in script

if(NumOrderQtr=43191,$(vAvgOrderCost),0) // expression

Re: Using IF with a variable in expression

ashokunbi — Mon, 17 Sep 2018 15:39:53 GMT

Thanks for your help.

I tried the first suggestion and it didn't work.

I added the second suggestion in the script and it blew loading. I think it's because it didn't find the field OrderQtr. This field is derived in my preceding lines in the script. Here is my script with your suggestion added

Load

OrderDate,

week(OrderDate) as OrderWeek,

month (OrderDate) as OrderMonth,

year (OrderDate) as OrderYear,

QuarterName(OrderDate,0,1) as OrderQtr,

Num(QuarterStart(OrderQtr)) as NumOrderQtr (Yours)

Re: Using IF with a variable in expression

rubenmarin — Mon, 17 Sep 2018 15:43:56 GMT

Hi Ade, in that case you should use OrderDate:

Num(QuarterStart(OrderDate)) as NumOrderQtr

Re: Using IF with a variable in expression

ashokunbi — Mon, 17 Sep 2018 15:51:21 GMT

Loaded but still getting $0 in the expression result

Re: Using IF with a variable in expression

rubenmarin — Mon, 17 Sep 2018 16:14:26 GMT

Can you upload a sample to check?

Re: Using IF with a variable in expression

ashokunbi — Mon, 17 Sep 2018 18:54:45 GMT

I replied to your e-mail with the file attached. I didn't know a way to upload here.

Re: Using IF with a variable in expression

rubenmarin — Tue, 18 Sep 2018 06:53:07 GMT

Hi Ade, the document didn't reached my email, to upload a file use advanced editor:

Below you have the option to attach:

You can create a new document with dummy data to show the problem (just to not upload real data) or use File -> Reduce Data -> Keep Possible Values to reduce the data uploaded.

Re: Using IF with a variable in expression

ashokunbi — Tue, 18 Sep 2018 14:23:45 GMT

Here you go

Re: Using IF with a variable in expression

rubenmarin — Tue, 18 Sep 2018 14:31:19 GMT

I see whats happening, you don't have a dimensión, so the if() always returns null() because each row has all quarternumbers, usually to add filters other than dimensions you need to use set analysis, wich will broke the use you are doing of variables, or you will need to pass the quarter as parameter

One quick fix can be adding an aggr to the expression, so each row will virtually check the quarters one by one:

Sum(Aggr(if(NumOrderQtr=43191,$(vAvgOrderCost),0), NumOrderQtr))