Jump to content
We've recently updated our Privacy Statement, available here. ×

Cumulative Sum AllPrevious excluding the first node or value


Thien Ung

Recommended Posts

Greetings Spotfire Community and Gurus:

I have a Y-Axis Custom Expression that currently tallies up the # of Work Orders created (A) Over All Periods and (B) Over All Periods per Year.

 

A.SUM(If([Attribute]"CLOSED",1,-1)) THEN Sum([Value]) OVER (AllPrevious([Axis.X]))

B. SUM(If([Attribute]"CLOSED",1,-1)) THEN Sum([Value]) OVER (Intersect(AllPrevious([Axis.X]),NavigatePeriod([Axis.X],"Year",0,0)))

 

 

How do Ire-write the expression so that it ignores the 1st month Or, if I expand it to AllPrevious period, how do I re-write it to ignore the 1st node or period

 

Attached are the EXCEL raw and unpivot data tables as well as the sample Spotfire DXP file to better illustrate the question.

Thanks in advance for your support.

Link to comment
Share on other sites

  • 1 month later...

Are you looking to ignore the first month and start the cumulative sum starting from second month

 

Please check if the following expressions work:

SUM(If([Attribute]"CLOSED",1,0)) THEN Sum([Value]) OVER (AllPrevious(NavigatePeriod([Axis.X],"Month",-1))) AS [CUMULATIVE CREATED],

SUM(If([Attribute]"CLOSED",1,-1)) THEN Sum([Value]) OVER (AllPrevious(NavigatePeriod([Axis.X],"Month",-1))) AS [NET CREATED]

 

A detailed explanation on writing similar expressions can be found here:

https://docs.tibco.com/pub/sfire-analyst/10.4.0/doc/html/en-US/TIB_sfire-analyst_UsersGuide/ncfe/ncfe_advanced_custom_expressions.htm

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...