Hi all,
Sorry bad topic…
I need to calculate a running total but need to reset the total on a condition.
I have this table - Date is the only dimension - all the others are table calculations:
Date, Registrations, Expected Registrations, Expected reached
2020-03-01, 5, 4,1
2020-03-02, 7, 5,1
2020-03-03, 8, 6,1
2020-03-04, 2, 5,0
2020-03-05, 5, 4,1
2020-03-06, 7, 5,1
2020-03-07, 8, 6,1
2020-03-08, 2, 5,0
Expected result with running total - the condition is that while “Expected Reached” <> 0 running total should be calculated. If “Expected Reached” = 0 the running total should start over from 0:
Date, Registrations, Expected Registrations, Expected Reached, Running Total
2020-03-01, 5, 4,1, 1
2020-03-02, 7, 5,1, 2
2020-03-03, 8, 6,1, 3
2020-03-04, 2, 5,0, 0
2020-03-05, 5, 4,1, 1
2020-03-06, 7, 5,1, 2
2020-03-07, 8, 6,1, 3
2020-03-08, 2, 5,0, 0
Any ideas or suggestions?
I’ve had that same challenge a few months ago and could not find any “Looker” way of doing it. I think you’re going to have to figure that out in SQL with a combination of at least 2 window functions and a subquery or CTE.
Thanks @Cyril_MTL_Analy
I was hoping for a combination of table calculations…But will look into a sql solution…
@izzymiller Any suggestions from you?
Thanks to the amazing solution from @jonallen
coalesce(if(${expected_reached} = 0, 0, sum(offset_list(${expected_reached}, 0-(row()-coalesce(max(offset_list(if(${expected_reached} = 0, row(), 0), 0-row(),
row())), 0)-1), row()-coalesce(max(offset_list(if(${expected_reached} = 0, row(), 0), 0-row(),
row())), 0)))), 0)
The logic is like this:
Hi @cristian2 and @Cyril_MTL_Analy
Please see the solution above
OMG! I have to try this!
Imagine a table function for this: running_total(value_column, column_1)
Works perfect!
How would you get this formula to work if the first value at the 0 condition isn’t zero? As you can see below, the running total is kicking in a row too late.
Hi! Can someone help with what the adjustment to the formula above that would peak running total to the first row for each “streak”?