Francisco Aspillaga Posted February 24, 2023 Share Posted February 24, 2023 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, Link to comment Share on other sites More sharing options...
Martin Yergeau Posted February 24, 2023 Share Posted February 24, 2023 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 asDEFINE FILE ABCNMeasYellow /I5 MISSING ON = IF <YOURDATE> GT <TODAYDATE> THEN MISSING ELSE MeasYellow;ENDGRAPH FILE ABCSUM MeasGrey NMeasYellowBY Dim.....END Link to comment Share on other sites More sharing options...
Fredrik Rosell 3 Posted February 27, 2023 Share Posted February 27, 2023 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 expressionSum([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 More sharing options...
Francisco Aspillaga Posted March 2, 2023 Author Share Posted March 2, 2023 Hi Fredrik,If I have data with value "0" the formula does not work?. Thanks for your help.Francisco Link to comment Share on other sites More sharing options...
Fredrik Rosell 3 Posted March 3, 2023 Share Posted March 3, 2023 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now