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

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

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,
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")

}