Calculating YTD for each week in table

jrtomici · 2025-03-07T18:31:55+00:00

There was an error rendering this rich post.

Hi, I want to replicate a table that looks like this:

My dataset currently has date, impressions, and goal, and in ETL I've mapped the dates to a week by joining with another table. How would I use beast mode to find the impressions to date for each week, as well as the delivery percentage of goal?

Find more posts tagged with

Magic ETL

Table

Beast Mode

Accepted answers

DavidChurchman

You can use a window function to create a running total column:

sum(Sum(Impressions)) over(order by Week)

And then you can create another one that divides that by your goal. (Not sure if that goal is in another column or what, so just using roughly what it seems to be based on your data):

sum(Sum(Impressions)) over(order by Week) / 915820

I used a week number to be safe. I think your week format should work, since it should sort correctly.

Manasi_Panov

Hi @jrtomici,

Check this video from Jae Wilson. I think it answers your question:

https://youtu.be/ZPf41Fjn1H8?si=KanaumUDF13JI48e

All comments

DavidChurchman

You can use a window function to create a running total column:

sum(Sum(Impressions)) over(order by Week)

And then you can create another one that divides that by your goal. (Not sure if that goal is in another column or what, so just using roughly what it seems to be based on your data):

sum(Sum(Impressions)) over(order by Week) / 915820

I used a week number to be safe. I think your week format should work, since it should sort correctly.

jrtomici

@DavidChurchman perfect, thank you!

jrtomici

Hi @DavidChurchman - bumping this to ask a similar question. Now I have this table I'd like to replicate:

Notice how the impressions to date are unique for the segment names and weeks.

Here is what my table in Domo looks like:

(Ignore the extra rows of data.) See how the impressions to date don't "reset" for the second segment name. Here is the beast mode I'm using for that column:

SUM(SUM(`Impression_Count`)) OVER(ORDER BY `Segment_Name`, `Week Number`)

And here is how I've sorted the card in Analyzer:

Is this possible?

Manasi_Panov

Hi @jrtomici,

Check this video from Jae Wilson. I think it answers your question:

https://youtu.be/ZPf41Fjn1H8?si=KanaumUDF13JI48e

jrtomici

@Manasi_Panov exactly what I was looking for, thank you!