30 Day Interval

cmarino
cmarino Member
in Beast Mode

Hello! I am looking to create a beastmode that sums video views and revenue separately in the 30-days after the asset is published. While the beastmode is validating and looks to be calculating accurately, the QA is very off. In the screenshot example, I manually filtered the time frame for 30-days after the published date and video starts and revenue are significantly off from what the beast modes are reporting. Below is the language I'm using, does anyone know what the issue may be?

SUM(
CASE
WHEN (date <= ADDDATE(published_date,interval 30 day) AND date >= published_date) AND DATEDIFF(CURRENT_DATE(),published_date) > 30 THEN estimated_partner_revenue WHEN date > ADDDATE(published_date,interval 30 day) THEN 0 END
)

30 Day Intervals.png

Tagged:

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    If you feel that your beast mode is correct, the first thing I would check would be your date range filter settings. Check to see which field is being used in the date range as this can often skew results if it is basing it off a date field that you weren't expecting it to.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • cmarino
    cmarino Member

    Hi Mark, thanks for your response! The challenge is we are looking to pull average 30-day interval performance across over 100 assets within different timeframes. Does the beastmode not force the timeframe filter to act as no more than 30 days? Do you have a recommendation to achieve that?

  • DataMaven
    DataMaven Coach

    I recommend adding all of the fields which are used in your beast mode into your table and scan through to see where the issue is. You can also take out the second WHEN statement. It should not have an impact, but may make things easier to review.

    Have you checked what Mark suggested? That is something that has come up countless times for clients I have worked with.

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories