Using IF with a variable in expression

[ashokunbi]

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

[ashokunbi]

I did. Didn't work.

[rubenmarin]

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

[ashokunbi]

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)

[rubenmarin]

Hi Ade, in that case you should use OrderDate:

Num(QuarterStart(OrderDate)) as NumOrderQtr

[ashokunbi]

Loaded but still getting $0 in the expression result

[rubenmarin]

Can you upload a sample to check?

[ashokunbi]

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

[rubenmarin]

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.

[ashokunbi]

Here you go

[rubenmarin · Accepted solution]

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))