2 Replies Latest reply on Sep 26, 2016 7:15 AM by adam wood

    Calculation using most recent <n> samples with a month filter

    adam wood

      I feel I am breaking a cardinal sin here, I am trying to replicate a bit of code I have made in R to tableau so that I do not have to link a SHINY page to my dashboard or have a R server constantly running. I apologize in advance as I am really new to Tableau...but I am trying!

       

      I am trying to calculate two values (geometric mean and estimated 90th percentile) by station ID. However, The formula is based only on the most recent 30 samples and I would like to make a MONTH filter so that the user can define a "season" from which values that go into the equations are pulled from. I think my biggest hurdle is to get the most recent 30 [FC conc] values per [StationID]

                        geomean=exp(mean(log(FC))),  #geometric mean

                        EST90=exp((mean(log(FC)))+((sd(log(FC))*1.28)))),  # estimated 90th percentile

       

      my R code:

       

      season <- subset(test, month <= 5 | month >= 11) #double slider #1

      NSSP <- function(x) {
        DTS <- x[, list(FC=tail(FC_conc, n = 30), date=tail(tmpdate, n = 30), month=tail(month, n = 30)), by=StationID] # reduces each station to most recent 30 samples
        DTS <- DTS[, list(new=date[length(date)],
                        old=date[1],
                        count=length(date),
                        geomean=exp(mean(log(FC))),  #geometric mean
                        EST90=exp((mean(log(FC)))+((sd(log(FC))*1.28)))),  # estimated 90th percentile
                        by = StationID]
        status <- data.frame(status=ifelse(DTS$geomean <= 14,ifelse(DTS$EST90 <= 31.5,"open","closed"),"closed"),StationID=DTS$StationID)
        output <- merge(DTS,status, by = "StationID")
       
      }