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?
Solved! Go to Solution.
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}
}