Cumulative Sum AllPrevious excluding the first node or value

[Thien Ung]

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.

[Neeraj Khatri]

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