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

Using AllPrevious with exception of first value

Victoria Harvey 3

Recommended Posts

  • 1 month later...
  • 1 year later...

Hi Khushboo - Do you have the DXP you can upload and share Spotfire Analyst 7.11 is complaining about the "expression is not valid" for the 2nd portion of the expression to exclude the FirstNode data.


This is my original expression: Sum([MTH_BETWEEN]) THEN Sum([Value]) OVER (AllPrevious([Axis.X])) AS [OVERDUE]


This is my expression to exclude the FirstNode data from the cumulative that is not working using your example provided:

Sum([MTH_BETWEEN]) OVER (AllPrevious([Axis.X])) - Sum([MTH_BETWEEN]) OVER (FirstNode([Axis.X])) AS [OVERDUE EXCLUDING 1ST NODE]



Link to comment
Share on other sites

  • 3 weeks later...

Terrific. Thank you Khushboo!! 


I just noticed something very odd. I took the expression you provided in your sample DXP and applied it directly to my test dataset. The visualization does not work in my example when I have the X-Axis as BinByDateTime as Year.Month. It only works when it is just Year. 


How would I change the Custom Expression for it to work when I have the X.Axis as Year.Month Thanks.


[[{"fid":"169981","view_mode":"default","fields":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false,"height":"","width":""},"type":"media","field_deltas":{"1":{"format":"default","field_file_image_alt_text[und][0][value]":false,"field_file_image_title_text[und][0][value]":false,"height":"","width":""}},"attributes":{"class":"media-element file-default","data-delta":"1"}}]]

Link to comment
Share on other sites

  • 3 months later...

It seems FirstNode only works as expected for one level of hierarchy i.e. whichever is the current level. You can try this workaround:


1) Create Mon1 calculated column which will have month values in integer format


Insert > Calculated Column...
   Column name: Mon1
   Expression: Integer(String(Month([Value])))


2) Then create a column with year month concatenation


Insert > Calculated Column...
   Column name: Period
   Expression: case  when Month([Value]) in (10, 11, 12) then Concatenate(Year([Value]),[Mon1]) else Concatenate(Year([Value]),0,[Mon1]) end


3)  Then in custom expression, you can rank by this period column and exclude values whose rank is 1 i.e. first node of Axis.X


Count(case  when DenseRank([Period],"asc",[Year])>1 then [Attribute] end) OVER (Intersect(AllPrevious([Axis.X]),NavigatePeriod([Axis.X],"Year",0,0)))


Above expression will exclude the value of first node. 


Now, if you want to hide first node, you can use Show/Hide items to show only node without 0 value


Expression - THEN [Y.FinalValue]

Rule Type - Not equal to 0

Show Items

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