Line graph cumulative sum

[Francisco Aspillaga]

Hi,

I have a graph that shows the cumulative sum. Thegray line is fine and OK, but I need the yellow line to be shown only up to the date that has data (Feb-23), since from that date it show displayed in constant value.

Regards,

[Martin Yergeau]

This question is tagged to Spotfire, but displayed under WebFOCUS....

I don't know how your data look like, but a solution could be to replace any data after current data for the "yellow" measure by NULL (MISSING in WebFOCUS)

Something such as

DEFINE FILE ABCNMeasYellow /I5 MISSING ON = IF <YOURDATE> GT <TODAYDATE> THEN MISSING ELSE MeasYellow;ENDGRAPH FILE ABCSUM MeasGrey NMeasYellowBY Dim.....END

[Fredrik Rosell 3]

Hello,

I assume this is indeed a Spotfire question so I have now removed the WebFOCUS topic.

Maybe you could do something like this:

Starting with a normal cumulative sum expression like this (that should result in something similar to your gray and yellow lines)

Sum([Measurement1]) THEN Sum([Value]) OVER (AllPrevious([Axis.X])) AS [Measurement1]

I changed that into this:

Sum([Measurement1]) THEN If(Sum(If([Value] is not null,1,0)) OVER (AllNext([Axis.X]))>0,Sum([Value]) OVER (AllPrevious([Axis.X])),Null) AS [Measurement1]

Explanation: In each point, this looks at the values in AllNext i.e. later dates. If they are all null, the current value will be null.

Example plot, using this expression

Sum([Measurement1]) THEN If(Sum(If([Value] is not null,1,0)) OVER (AllNext([Axis.X]))>0,Sum([Value]) OVER (AllPrevious([Axis.X])),Null) AS [Measurement1],

Sum([Measurement2]) THEN Sum([Value]) OVER (AllPrevious([Axis.X])) AS [Measurement2]

In this example, the orange Measurement line stops at 10/12/2022 as there are no values after that.

 

[Francisco Aspillaga]

Hi Fredrik,

If I have data with value "0" the formula does not work?.

 

Thanks for your help.

Francisco

[Fredrik Rosell 3]

Hello Francisco,

Well, if the definition of "doesn't have data" is that the value is 0, you should just check for that instead. As you appear to have floats, well, floating point comparison is messy (the internet is full of documentation on that topic) and I will let you decide how to best make that comparison to get the results that you need. Note: There is no built in "almost equal" function so you will need to handle that.

In my original example, I interpreted no data to mean null data so that's what I checked for. You could simply check for values instead.

As a very trivial example below (I'm NOT stating that this is the right way to do it - please refer to the above mentioned available documentation on floating point comparisons), I instead define positive values larger than 0.000001 to mean that that there is data, so I check for If([Value]>=0.000001

Sum([Measurement1]) THEN If(Sum(If([Value]>=0.000001,1,0)) OVER (AllNext([Axis.X]))>0,Sum([Value]) OVER (AllPrevious([Axis.X])),Null) AS [Measurement1]