SPOTFIRE how do I shape by column in the map chart with a custom expression

Question

I have a spotfire question. I am attempting to create Shapes by Column Values in a Map Chart.

I would like to create these Shapes by a Custom Expression. The custom expression is below, I have simplified it so it is easier to read.

All I am saying is:

if((Current months oil rate - 12MonthAgo Oilrate)/12MonthAgo Oilrate)>0,"UP","Down")

When I run this calculation though it only gives me one value, (there are both positives and negatives so it should give two).

I am not sure what I have done wrong? Any help is appreciated.

 <If(((((Sum(If(CurrentMonth),[OILRATECD],null))
    - 
Sum(If(12MonthsAgo),[OILRATECD],null)))))
    /
Sum(If(12MonthsAgo),[OILRATECD],null)))>0,"UP","DOWN")>

ORIGINAL EQUATION:

 <If(((((Sum(If((Month([DATE])=Month(DateAdd("mm",-2,DateTimeNow()))) and (Year([DATE])=Year(DateAdd("mm",-2,DateTimeNow()))),[OILRATECD],null))
    - 
Sum(If((Month([DATE])=Month(DateAdd("mm",-${MonthInterval}-2,DateTimeNow()))) and (Year([DATE])=Year(DateAdd("mm",-${MonthInterval}-2,DateTimeNow()))),[OILRATECD],null)))))
    /
Sum(If((Month([DATE])=Month(DateAdd("mm",-${MonthInterval}-2,DateTimeNow()))) and (Year([DATE])=Year(DateAdd("mm",-${MonthInterval}-2,DateTimeNow()))),[OILRATECD],null)))>0,"UP","DOWN")>

scsimon scsimon · Accepted Answer · 2017-03-28T18:56:10

First, you have a lot of unnecessary parentheses but that shouldn't hurt anything.

If(
    (
        ( --this open parentheses is unneeded
            ( --this open parentheses is unneeded
                (
                    Sum(If((Month([DATE])=Month(DateAdd("mm",-2,DateTimeNow()))) and (Year([DATE])=Year(DateAdd("mm",-2,DateTimeNow()))),[OILRATECD],null))
                    - 
                    Sum(If((Month([DATE])=Month(DateAdd("mm",-${MonthInterval}-2,DateTimeNow()))) and (Year([DATE])=Year(DateAdd("mm",-${MonthInterval}-2,DateTimeNow()))),[OILRATECD],null))
                )
            ) --this closed parentheses is unneeded
        ) --this closed parentheses is unneeded
        /
        Sum(If((Month([DATE])=Month(DateAdd("mm",-${MonthInterval}-2,DateTimeNow()))) and (Year([DATE])=Year(DateAdd("mm",-${MonthInterval}-2,DateTimeNow()))),[OILRATECD],null))
    )
    >0,"UP","DOWN")

The reason you are not getting UP and DOWN returned is because the condition isn't met. We need a sample data set with expected output to verify this.

However, here's a reason why you could be getting unexpected results, regarding NULL in your SUM(IF(...,[OILRATECD],NULL)) expression.

TL;DR

If your condtion in your IF() statement isn't ever evaluated to true then NULL is returned to SUM(), and SUM(NULL,NULL,NULL) = NULL and NULL is not > nor is it < 0, thus your outer IF() statement would return NULL instead of "UP" or "DOWN"

LONG VERSION

Spotfire ignored NULL when evaluating SUM() which is what you want. For example, Sum(4,3,NULL) = 7 and this is the behavior we want. However...
Spotfire doesn't ignore NULL for addition, subtraction, division, and other comparison operators like >. So, 4 - NULL = NULL and NULL / 18 = NULL and so on. This means if either of your two SUM() methods return NULL then your entire expression will be NULL because...
NULL isn't > nor is it < and certainly not = 0. NULL is the absence of a value, and thus can't be compared or equated to anything. For example, If(NULL > 1,"YES","NO") doesn't return YES or NO... it returns NULL, the lack of a value. Also, If(NULL=NULL,"YES","NO") will also return NULL

HOW TO GET AROUND THIS

Use IS NULL and IS NOT NULL in a IF() statement to set it to a default value, or use 0 in place of NULL in your current expression.

Sum(If((Month([DATE])=Month(DateAdd("mm",-2,DateTimeNow()))) and (Year([DATE])=Year(DateAdd("mm",-2,DateTimeNow()))),[OILRATECD],0))

BIG SIDE NOTE

You said your equation's pseudo code is:

If((Current months oil rate - 12MonthAgo Oilrate)/12MonthAgo Oilrate)>0,"UP","Down")

This doesn't seem to be what you are evaluating. Instead, I read:

x = (-var) + (-2) thus var < -2 (i.e. -3....-6...-55)
if((sum([2 months ago oil rate]) - sum([x months ago oil rate])) > 0, "UP","DOWN")

So you aren't ever looking at the current month's oil rate, but instead are looking at current - 2. Also, you are looking at the SUM() over that entire month... this may be what you want by you may be actually looking for Max() or Min()

OVER FUNCTION

You can handle this a lot easier most likely with a few calculated columns to keep your data / expressions clean and legible

DatePart("yy",[Date]) as [Year]
DatePart("mm",[Date]) as [Month]
Max([OILRATECD]) OVER (Intersect([Year],[Month])) as [YearMonthRate] or use SUM() if that's what you really want.
Check to see the difference with Rank()

SPOTFIRE how do I shape by column in the map chart with a custom expression

1 Answers