Using AllPrevious with exception of first value

[Victoria Harvey 3]

I am trying to sum values with the exception of the very first value. Is there a way to do this I currently have this which includes the first value.

[Hits per code (count)] THEN Sum(([Value] / Avg([Value])) - 1) OVER (AllPrevious([Axis.X])) as [iNL]

 

How can I perfor the equation on n=1:n-1

 

Thanks!

[Khushboo Rabadia]

If you want to ignore the current value you can have expression as below:

Sum([Val]) OVER (AllPrevious([Axis.X])) - Sum([Val])

If you want to ignore the first value as shown in attached screenshot you can have below expression:

Sum([Val]) OVER (AllPrevious([Axis.X])) - Sum([Val]) OVER (FirstNode([Axis.X]))

[Thien Ung]

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]

 

Thoughts

[Khushboo Rabadia]

It works even in 7.11. Sample dxp attached.

[Khushboo Rabadia]

It works even in 7.11. Sample dxp attached.

[Thien Ung]

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"}}]]

[Khushboo Rabadia]

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