CODEWITHSUNDEEP
spotfire
Jon
Jon

Reputation: 57

Spotfire: Sum() OVER (Previous()) blanking out second node when used with If() statement

I have the situation where I have a table that looks like the following:

Year   Sales
2000   100
2001   225
2002   375
2003   490
...

What I want to do is:

Here's what I am trying to achieve:

Year   Sales   NewColumn
2000   100     50        <For now, use Sales * 0.5>
2001   225     125
2002   375     150
2003   490     115
...

Here's the custom expression I'm using in a custom column:

If(
  Sum([Net Sales]) over (Previous([Year])) is null,
  (Sum([Net Sales]) over ([Year])) * 0.5,
  ([Sales] - Sum([Sales]) over (Previous([Year]))) 
)

However, this is what my formula returns:

Year   Sales   NewColumn
2000   100     50      
2001   225     <seems to be blank>
2002   375     150
2003   490     115
...

Now, I'm confused at this point: the Previous statement will return null when looking at the first node. So if I ran ([Sales] - Sum([Sales]) over (Previous([Year]))) I would get:

Year   Sales
2000   
2001   225
2002   375
2003   490
...

... but when I couple this logic with an if statement, it seems like everything is "shifted" a row down. Any thoughts on what I'm (clearly) misinterpreting here? Why has the focus of Previous apparently moved one row down?

(For clarity, this extends on my previous SO post here: link)

Upvotes: 0

Views: 1481

Answers (1)

Monte_fisto
Monte_fisto

Reputation: 745

The issue is with your if statement. It is excluding the row sets where Sum([Sales]) over (Previous([Year])) are null from calculations where Sum([Sales]) over (Previous([Year])) is not null.

I would use SN([Sales] - Sum([Sales]) over (Previous([Year])),Sum([Sales]) over ([Year]) * 0.5).

Upvotes: 0

Related Questions