Search code examples
formulacalculated-columnsspotfire

Sum over all previous, until certain point

I want to create a calculated column in Spotfire which sum the value until a certain point such that it starts over again summing the values. See below example. When there is a value in the Stocks column, the sum of the Volumes needs to restart again from that point etc.

enter image description here

Thanks!

Solution

  • Breaking it down, you can accomplish this by

    1- Calculate a column which groups rows based on the previous non null Stocks row [Group]

    last(case  when [Stocks] is not null then [Date] end) OVER (allPrevious([Date]))

    2- Create a Hierarchy contatining the grouping and date [Gp_Date_Hr]

    CREATE  NESTED HIERARCHY [Gp_Date_Hr]
  [Group] AS [Group],
  [Date] AS [Date]

    3- Calculate your desired value

    Sum([Volume]) OVER (Intersect(Parent([Hierarchy.Gp_Date_Hr]),AllPrevious([Hierarchy.Gp_Date_Hr])))