Jump to content

Line graph cumulative sum

Francisco Aspillaga

Recommended Posts

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

Link to comment
Share on other sites


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.


Link to comment
Share on other sites

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]

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...