Spotfire - Sum() over

Question

I'm trying to sum the values in column INDICATOR for the last 30 days from DATE, by account.

My expression is: Sum([INDICATOR]) over (Intersect([id],LastPeriods(30,[DATE]))) but the results are not accurate.

Any help is appreciated.

Sample data below:

    DATE    30DAYSBACK  ID  INDICATOR   RUNNING30   EXPECTED
    3/2/16  2/1/16  ABC 1   3   3
    3/2/16  2/1/16  ABC 1   3   3
    3/2/16  2/1/16  ABC 1   3   3  
    3/7/16  2/6/16  ABC 1   7   7
    3/7/16  2/6/16  ABC 1   7   7
    3/7/16  2/6/16  ABC 1   7   7
    3/7/16  2/6/16  ABC 1   7   7
    3/8/16  2/7/16  ABC 1   10  10
    3/8/16  2/7/16  ABC 1   10  10
    3/8/16  2/7/16  ABC 1   10  10
    3/10/16 2/9/16  ABC 1   12  12
    3/10/16 2/9/16  ABC 1   12  12
    3/14/16 2/13/16 ABC 1   13  13
    3/15/16 2/14/16 ABC 1   14  14
    3/16/16 2/15/16 ABC 1   15  15
    3/21/16 2/20/16 ABC 1   16  16
    3/22/16 2/21/16 ABC 1   17  17
    3/23/16 2/22/16 ABC 1   19  19
    3/23/16 2/22/16 ABC 1   19  19
    3/25/16 2/24/16 ABC 1   20  20
    3/29/16 2/28/16 ABC 1   22  22
    3/29/16 2/28/16 ABC 1   22  22
    3/30/16 2/29/16 ABC 1   27  27
    3/30/16 2/29/16 ABC 1   27  27
    3/30/16 2/29/16 ABC 1   27  27
    3/30/16 2/29/16 ABC 1   27  27
    3/30/16 2/29/16 ABC 1   27  27
    3/31/16 3/1/16  ABC 1   29  29
    3/31/16 3/1/16  ABC 1   29  29
    4/1/16  3/2/16  ABC 1   31  31
    4/1/16  3/2/16  ABC 1   31  31
    4/4/16  3/5/16  ABC 1   32  29
    4/5/16  3/6/16  ABC 1   33  30
    4/13/16 3/14/16 ABC 1   34  27
    4/13/16 3/14/16 ABC 1   34  27
    4/13/16 3/14/16 ABC 1   34  27
    4/13/16 3/14/16 ABC 1   34  27
    4/15/16 3/16/16 ABC 1   35  24
    4/20/16 3/21/16 ABC 1   31  26
    4/20/16 3/21/16 ABC 1   31  26
    4/20/16 3/21/16 ABC 1   31  26
    4/25/16 3/26/16 ABC 1   31  25
    4/25/16 3/26/16 ABC 1   31  25
    4/25/16 3/26/16 ABC 1   31  25
    4/26/16 3/27/16 ABC 1   31  26
    4/27/16 3/28/16 ABC 1   34  29
    4/27/16 3/28/16 ABC 1   34  29
    4/27/16 3/28/16 ABC 1   34  29
    4/27/16 3/28/16 ABC 1   34  29
    4/28/16 3/29/16 ABC 1   35  30

Thanks for the data set, however it's missing the [INDICATOR] column — S3S
Sorry! [INDICATOR] is effectively just a value of 1 straight down. (Mislabeled above... seen as 'IDENTIFIER' above.) — Christine Kang
Your formula is working correctly. The problem you are running into is that you don't have a row for every day. It is doing the SUM() correctly for the past 30 PERIODS based on the sorting of your data when you load it into Spotfire. It's hard to suggest an alternative because I don't know what the end goal really is. Can you elaborate on that? — S3S
What I'm looking to get is a sum of indicators on an account that occurred in the past 30 days. So, for example, if I'm looking at 4/28/2016, I want to sum the indicators beginning and including 3/29/2016 (30 day lookback) to 4/28/2016 (inclusive). — Christine Kang

TxAggie2007 TxAggie2007 · Accepted Answer · 2016-06-16T20:03:52

I wan't able to determine a suitable solution within Spotfire. However, I was able to write some R code that allows for the Date and Indicator columns to be passed to it, sums the indicator for the past 30 days, and then returns this as a column.

Here is the code:

record.date <-as.Date(date.column, "%m/%d/%Y")
indicator.vector <- indicator.column

num.entry <- length(record.date)
running.thirty <- vector(length=num.entry)
count <- 0

for(i in 1:num.entry){
  date.test <- record.date[i]
  for(j in 1:num.entry){
    if(record.date[j] >= date.test-30 ){
      if(record.date[j] <= date.test){
        count <- count + indicator.vector[j]
      }
    }
  }
  running.thirty[i] <- count
  count <- 0  
}

output<-running.thirty

Use Tools >> Register Data Function

(1) Insert the script

(2) Create the two input parameters:

input parameters

(3) Create the output parameter:

output parameter

NOTE: I think there are some errors in your expected values near the end of your data set.

Spotfire - Sum() over

1 Answers