Calculating YTD for each week in table

jrtomici
jrtomici Member
in Beast Mode

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

Screenshot 2025-03-07 at 1.25.59 PM.png

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?

Tagged:

Best Answers

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    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

    image.png

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

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hi @jrtomici,

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

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

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Answers

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    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

    image.png

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

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • jrtomici
    jrtomici Member

    @DavidChurchman perfect, thank you!

  • jrtomici
    jrtomici Member

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

    image.png

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

    Here is what my table in Domo looks like:

    image.png

    (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:

    image.png

    Is this possible?

  • Manasi_Panov
    Manasi_Panov Contributor
    Answer ✓

    Hi @jrtomici,

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

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

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • jrtomici
    jrtomici Member

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

Categories