Adding Multiple Cumulative Sum

[John]

Hi!

Would it be possible to add multiple cumulative sum expressions?  I want to get the delta of the following expression

1st  Expression: SN(Sum([New_Backlog]) OVER (AllPrevious([Axis.Columns])),0)

2nd Expression: SN(Sum([New_Early]) OVER (AllPrevious([Axis.Columns])),0)

3rd Expression: Sum([New_Commit Qty]) Then Sum([Value]) OVER (AllPrevious([Axis.Columns])) as [Cumm Commit]

4th Expression: Sum([FGOUT])/Count([ExtractDate]) THEN Sum([Value]) OVER (AllPrevious([Axis.Columns])) as [Cumm Out]

Delta Forumul

3rd Expression + 2nd Expression - 1st Expression - 4th Expression

 

 

 

 

 

 

[Olivier Keugue Tadaa]

Hi John

The answer to your question  is YES however you  will put the delta into one large expression like this one:
SN(Sum([New_Backlog]) OVER (AllPrevious([Axis.Columns])),0) +
SN(Sum([New_Early]) OVER (AllPrevious([Axis.Columns])),0) -
Sum([New_Commit Qty]) Then Sum([Value]) OVER (AllPrevious([Axis.Columns])) -
Sum([FGOUT])/Count([ExtractDate]) THEN Sum([Value]) OVER (AllPrevious([Axis.Columns]))
as [Delta]

If your question is about if you can first define the first 4 expressions and re-use them in the delta (like this 3rd Expression + 2nd Expression - 1st Expression - 4th Expression), then the answer is NO (at least for customs expressions). 

I hope this helps.

[John]

im having an error when i try ti copy your expression. can help?

[David Boot-Olazabal]

Hi John,

This typically means that the [New_Commit Qty] column doesn't exist in the dataset.
What is the error message? You should have a red exclamation mark icon at the right end of the formula, explaining what the error is. Also, can you verify if this column is available in your data table (used for the formula)?

Kind regards,

David

[John]

This column suddenly turned red when applying a THEN on previous expression.

 

[David Boot-Olazabal]

Hi John,

What I can see in your formulas, is that all working constructs with THEN is preceded by a division. For instance, Sum([FGOUT])/Count([ExtractDate]) THEN...
In the one that doesn't work, there is no such division. That could be a possible problem.

But you also defined a formula twice as [Delta], that may also be a problem as Spotfire doesn't know which one should be taken into account as [Delta].

Could you check both?

Kind regards,

David

[John]

Hi David,

I already rename the [Delta] and I still facing the same error. I did try to remove the 4th expression and it's working. can we use two THEN statements in an expression?

 

[Olivier Keugue Tadaa]

Hi John, sorry for my late reply.
Please use this one (I have removed the THEN since it is usually used for optimisation purpose or when you have in-db data source).

SN(Sum([New_Backlog]) OVER (AllPrevious([Axis.Columns])),0) + 
 SN(Sum([New_Early]) OVER (AllPrevious([Axis.Columns])),0) - 
Sum([New_Commit Qty]) OVER (AllPrevious([Axis.Columns])) - 
Sum([FGOUT]) / Count([ExtractDate]) OVER (AllPrevious([Axis.Columns])) as [delta]

It should work now

Or if you should use THEN,

Since after THEN you can only use Value (assuming it contains the previously calculated metrics),

and all calculations are made against the same OVER (AllPrevious([Axis.Columns]))

then you have this alternative expression with the same result, while keeping the benefits you had with the THEN

SN(Sum([New_Backlog]),0) + 
SN(Sum([New_Early]),0) - 
Sum([New_Commit Qty]) - 
Sum([FGOUT]) / Count([ExtractDate]))

THEN sum([Value])

OVER (AllPrevious([Axis.Columns]))

AS [delta]

Please let us know if it works for you

Edited May 1 by Olivier Keugue Tadaa

[John]

HI Oliver,

Yes it works!. Thanks!

[John]

Hi!

Regarding the Over Function, is it possible to do division on below two expressions to get the rate? 1st Expression/2nd Expression

1st Expression: SN(Sum([FGOUT]) / Count([FGOUT]),0) + SN(Sum([New_Early]),0) - SN(Sum([New_Backlog]),0)  - SN(Sum([New_Commit Qty]),0) THEN sum([Value]) OVER (AllPrevious([Axis.Columns])) as [Delta]

2nd Expression: Sum(-[New_Commit Qty]) as [Commit]