User Guide

Getting Started

Help Center

Documentation

Community

Training

Certification

Solved

How can I replicate a Sum with an Over clause?

  • 1 April 2021
  • 5 replies
  • 290 views
humbletyler

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?

icon

Best answer by eric_h 1 April 2021, 22:05

View original
This topic has been closed for comments

5 replies

eric_h

Looker’s got your back! In the LookML, if you create a measure of type: sum using your added dimension, you can use that newly-created measure in a measure of type: running_total . Here’s an example:

 

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

measure: running_total_added {
    type: running_total
    sql: ${total_added}
}

Then, in your explore, you can grab those two measures along with the timestamp_date field to generate what it sounds like you’re looking for:

 

 

Note that I had to add those three fields and then click the “Order Items Created Date” dimension header in the table at the bottom to sort ascending to get an accurate running total. Hope this helps! Happy to elaborate on anything happening here :)

humbletyler

@eric_h Thank you for your reply!

 

What you have here is very close to what I need!  Unfortunately it doesn’t quite work for my purposes, because I need to compare a running total across several series.

 

I think I articulated my question better in another post I made this morning (apologies for spam), if you are curious.

 

But in your example, I’d also want to select and pivot off of an item_id, so that I could see the running totals per item.

 

If I do select/pivot off of the item_id, I don’t see the results I would expect… it seems that the running total is kept per-day for all items, still, instead of per-item for all preceeding days.

 

Thanks again so much for your help!

1 Attachment
eric_h

Ah! Sorry I missed the pivot part. I think the easiest way is to use a Table Calculation:

  • Take your date field, your pivoted grouping field (item_id) and the measure (sum_of_added) and generate an explore.
     
  • Once that finishes, you can create a Table Calculation (or Custom Field) with the expression running_total(${your_measure_column}) . That will get you the running total based on the current layout of your table.
     
    Table calculation is in green
  • Pros:
    • Easy calculation to make, flexible and applicable to any pivoting column
  • Cons:
    • The measure you want the running total for *must* be in the table for it to work. Though, for your use case, I think this is okay.

I tried doing this with a derived table, but it still doesn’t handle pivoting very well. For now, the table calculation is the path of least resistance, but there has to be a way to do it in LookML.

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

 

humbletyler

@eric_h Thank you so much!!  Both of these are great, the second is super clean so that’s what I’ll go with :)

 

Really appreciate you taking the time!