Using an IF statement in a variable

[#1]

I have been trying to put the below expression into a variable so I can exclude all instances where the result is within 10%.  If the variable works I want to use is in set analysis in the following way; {<vVariance11%=1>} hence pulling out the records with a variance of over 10%.

vVariance11%=if(([Under Distance]/[Original contract mileage])<=-0.1 and ([Under Distance]/[Original contract mileage])>= 0.1,0,1)

When I add this variable as a dimension in a straight table it always returns '1' and doesn't take into account the condition in the IF statement.  Can anyone point me in the right direction to make this work?

[vishsaggi]

Can you share a sample with an expected output you are looking for? Would be easy to look into and work on.

[oscar_ortiz]

Helen,

You may want to try adding that if statement to your script and creating a new [Variance Flag] column in your table.

If(

     ([Under Distance]/[Original contract mileage]) <= -0.1

     and

     ([Under Distance]/[Original contract mileage]) >= 0.1,0,1

) as [Variance Flag]

Then you could easily use that in your set analysis statement. {< [Variance Flag] = {1} >}.

Good luck

Oscar

[#4]

Hi Oscar

Many thanks for your suggestion.

I have tried adding the IF statement to the load script as you suggest above (excluding the = in case that was causing problems), see below. 

IF(([Under Distance]/[Original contract mileage])<-0.1 and ([Under Distance]/[Original contract mileage])>0.1,0,1) as [Variance Flag],

It still isn't applying the flag '0' to records falling within a 10% tolerance.  E.g. I have a variance of 0.07 and the '1' flag is being applied to it, along with every other record.

I have added the following calculation into the load script:

[Under Distance]/[Original contract mileage] as [Variance],

My thinking was maybe I could use this derived field as part of the IF statement.  However, while I can use it as a dimension, it does not help me capture only records with a variance of +/- 10%.

Does anyone have any other suggestions?

[oscar_ortiz · Accepted solution]

Helen,

I believe it's the if statement itself, try it with an OR instead of an AND.

Good luck

Oscar

[oscar_ortiz]

I've added an example document.

[#7]

Many thanks Oscar.  That worked perfectly.