CODEWITHSUNDEEP
calculated-columnsspotfire
Philip Connell
Philip Connell

Reputation: 651

Spotfire Between two values calculated column

I have a Calculated Column see syntax below.

What I trying to achieve is this. If there is a piece of text in [Part of Group ?] and there is RUBU or CABU in [ISPG Grouped] column then get 10% of [Final] column. But what I also want to say is that if there is no text in [Part of Group ?] and and there is RUBU in [ISPG Grouped] column and the value of [Final] column is between 10,000 euro and 34,999 then get 4% of [Final] column

Here is the syntax I have so far 

CASE  WHEN ([Part of Group ?]!="") AND ([ISPG Grouped]="RUBU") THEN [Final] / 100 * 10 
WHEN ([Part of Group ?]!="") AND ([ISPG Grouped]="CABU") THEN [Final] / 100 * 10
WHEN ([Part of Group ?] ="") AND ([ISPG Grouped]="RUBU") AND [Final] <> 10000 34999 THEN [Final] / 100 * 4
ELSE NULL END

The error is happening after 34,999 see Pic 1 below for more details

Basically I don't know how to do the If In between two values then execute function :-)

Pic 1: enter image description here

Upvotes: 2

Views: 3726

Answers (2)

S3S
S3S

Reputation: 25142

Mark P's answer will address the syntax error, but you have a flaw in your CASE logic. CASE expressions will exit once a condition is met. It doesn't check the rest of the conditions, so the order of your conditions matter here.

The first line: WHEN ([Part of Group ?]!="") AND ([ISPG Grouped]="RUBU") THEN will cause the last line to NEVER be evaluated because it contains the same minimal conditions and would exit. To fix this, swap them.

CASE  
    WHEN ([Part of Group ?]!="") AND ([ISPG Grouped]="CABU") THEN [Final] / 100 * 10
    WHEN ([Part of Group ?] ="") AND ([ISPG Grouped]="RUBU") AND [Final] > 10000 AND [Final] < 34999 THEN [Final] / 100 * 4
    WHEN ([Part of Group ?]!="") AND ([ISPG Grouped]="RUBU") THEN [Final] / 100 * 10 
ELSE NULL END

As you can see here, the second condition is more restrictive than the third thus some cases would fail here, but evaluate to true for the third case.

If you put when 1=1 then 1 at the top of your CASE expression, then the only value you'd ever see is 1. Similarly, if you placed it last... this would be equivalent to saying ELSE 1 since 1 always equals 1, on earth :)

Upvotes: 1

Mark P.
Mark P.

Reputation: 1827

"... the value of [Final] column is between 10,000 euro and 34,999 ..."

Could you change that part of your formula to this: ... AND [Final] > 10000 AND [Final] < 34999 THEN ...

If you want it to be inclusive, either change the operators to <= and >= or increment the numbers down and up 1 respectively.

Upvotes: 3

Related Questions