Running total with a condition or...?

cristian2 · 03-26-2020 01:48 PM

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?

Cyril_MTL_Analy

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.

cristian2

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?

xin

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:

Find the last row containing a zero max(offset_list(if(${expected_reached} = 0, row(), 0), 0-row(), row()))
Create a list of numbers starting at that last zero up to the current row
Sum the list
Wrap the whole thing in “if current row is zero, return 0”

xin

Hi @cristian2 and @Cyril_MTL_Analy

Please see the solution above

cristian2

OMG! I have to try this!
Imagine a table function for this: running_total(value_column, column_1)

cristian2

Works perfect!

Aaron_Schwartzb

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.

jon_lewis-16281

Hi! Can someone help with what the adjustment to the formula above that would peak running total to the first row for each “streak”?