Trailing Totals Deep Dive

marcel_luthi · 2023-07-28T14:17:01+00:00

There was an error rendering this rich post.

Yesterdays Community Meeting was awesome with lots of valuable insights, but was wondering if someone could elaborate a bit more on how the trend SQL works, I know we saw 2 of the transforms but I'm having a hard time figuring out the rest, like how is information being filled so that the rows 91-1 represent consecutive days.

Find more posts tagged with

SQL

Accepted answers

MarkSnodgrass

@marcel_luthi they key to consecutive days is to add the Domo Dimension Calendar which has a row for every day. Joining your data to that will ensure consecutive days.

All comments

MarkSnodgrass

@marcel_luthi they key to consecutive days is to add the Domo Dimension Calendar which has a row for every day. Joining your data to that will ensure consecutive days.

marcel_luthi

I get that we join the calendar one with each possible granularity we want to be able to break things to, which would be the base table to which we'll then left join all the trailing calculations we've performed in the other transforms (or at least I think that is why there are so many there), but I'm guessing we're also filling blanks for each granularity for those somehow so that there will no missing entries when we try to roll things up, but now that I write it down I guess I'm starting to get it, we'll do an append of this one to the raw data one, to ensure we have at least one entry per combination per day, and once we roll up since the other fields will be null these won't affect the calculation but ensure we're actually rolling up the last N calendar days and not the last N days with entries. (Sorry this might sound confusing)