Solved: How can I replicate a Sum with an Over clause? - Page 2

humbletyler · 03-31-2021 05:54 PM

I have some raw wishlist data in a simple form (timestamp, user_id, item_id, added) where the final item there is just a +1 or -1.

The data isn’t that clean, of course, but that’s effectively what I have 🙂

I want to be able to visualize both the day-to-day changes and the running totals on a line chart.

Having the running total is a bit complicated, and I can’t figure out the best way to do it in Looker.

In SQL, I would do something like:

SUM(added) OVER(PARTITION BY item_id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

And that would give me the running total; by selecting/grouping the days, I’d get 1x row per day/item combo and the useful data rows would be delta to tell me what the total change would be and running_total to tell me what the running total would be.

Can someone help me figure out how to accomplish this in Looker?

eric_h

Oh! I figured it out! There’s a direction: parameter in measures that lets Looker know how you want it to be calculated when pivoted.

Going back to the initial response I sent, you just need to add direction: “column” to the running total calculation, then it will work as expected.

measure: total_added {
    type: sum
    sql: ${added}
}

measure: running_total_added {
    type: running_total
    direction: "column"
    sql: ${total_added}
}

Total Profit column included for validation, not required to calculate running total

View solution in original post