Hello, I'm looking to see if it's possible to create a waterfall chart in Flexdashboard that would update the values based on a certain month for the data. The output in my head is that the months number (1-12) would be in a drop down list and users would be able to select what month they'd like and the waterfall would adjust accordingly based on that months data along with the calculations needed for the waterfall chart.
Data is currently setup in a data frame and the calculations below are what's needed to input into the waterfall chart. I've also included my waterfall code.
TTL_Budget <- sum(budget$total_cost)
TTL_ActualCost <- sum(actuals$TOTAL_CHARGE, na.rm = TRUE)
ttl_load_vol <- (sum(region$loads_a) - sum(region$loads_b)) * (sum(region$LinehaulCost_b)/sum(region$loads_b))
LinehaulRate <- sum(region$loads_a) *
((sum(actuals$LINEHAUL_CHARGE,na.rm = TRUE) / sum(region$loads_a)) - (sum(budget$linehaul_cost) / sum(region$loads_b)))
FuelVolume <- (sum(region$loads_a) - sum(region$loads_b)) * (sum(budget$fuel_cost) / sum(region$loads_b))
FuelRate <- sum(region$loads_a) *
((sum(actuals$FUEL_CHARGE, na.rm = TRUE) / sum(region$loads_a)) - (sum(budget$fuel_cost) / sum(region$loads_b)))
AccessorialRate <- sum(region$loads_a) *
((sum(actuals$ACCESSORIAL_CHARGE, na.rm = TRUE) / sum(region$loads_a)) - (sum(budget$accessorial_cost) / sum(region$loads_b)))
AccessorialVolume <- (sum(region$loads_a) - sum(region$loads_b)) * (sum(budget$accessorial_cost) / sum(region$loads_b))
Expense <- sum(TTL_Budget, ttl_load_vol, LinehaulRate,
FuelRate, FuelVolume, AccessorialRate, AccessorialVolume)
adjustment <- TTL_ActualCost - Expense
ActualExpense <- Expense + adjustment
Waterfall Chart
y = c(TTL_Budget, ttl_load_vol, LinehaulRate + adjustment,
FuelRate, FuelVolume, AccessorialRate, AccessorialVolume, ActualExpense)
x = c("Budget Total Expense", "Total Loads (Volume)",
"Linehaul Rate", "Fuel Rate", "Fuel Volume","Accessorial Rate", "Accessorial Volume", "Actual Total Expense")
text = paste('$',format(round(c(TTL_Budget, ttl_load_vol, LinehaulRate + adjustment,
FuelRate, FuelVolume, AccessorialRate, AccessorialVolume, ActualExpense)),0, big.mark = ","))
measure = c("absolute", "relative", "relative",
"relative", "relative","relative", "relative", "total")
data = data.frame(x = factor(x,levels = x), y, measure, text)
fig <- plot_ly(data, x = ~x, y = ~y, measure = ~measure, textposition = "outside", text = ~text, type = "waterfall",
base = 0,
decreasing = list(marker = list(color = "Green")),
increasing = list(marker = list(color = "Red")),
absolute = list(marker = list(color = "Grey")),
totals = list(marker = list(color = "Grey")))
waterfall <- fig %>%
layout(title = "Inbound Variance Waterfall Analysis", xaxis = list(title = "", tickfont = "16", ticks = "outside"),
yaxis = list(title = "Total Expense", tickprefix = '$'), waterfallgap = "0.1")
waterfall