can anyone point me to the solution on how to build the following year to date vs previous year?

German_Rodriguez
German_Rodriguez Member
in Cards, Dashboards, Stories

I have a feed that collects daily sales and units, I need to create a report that generates a table showing the following information

the previous day, week-to-date (WTD), month-to-date (MTD), quarterly-to-date (QTD), and year-to-date (YTD) vs. the previous year

2024-05-15_09-32-29.png

Best Answer

  • Sean_Tully
    Sean_Tully Contributor
    Answer ✓

    For MTD and WTD, you can use the dayofmonth() and dayofweek() functions to get the numerical value of the previous day in terms of week or month, and then compare to last year's to make sure the same value is less than or equal.

    QTD and YTD can get tricky depending on if you're using a special fiscal year or not. If it's just the straight calendar year, the dayofyear() function should work. If not, you'll have to make some adjustments in the beast mode. Someone else might know how to do QTD in an easy way, but I think you'd have to define each quarter in the beast mode and then adjust the value you get from the dayofyear() function accordingly.

Answers

  • Sean_Tully
    Sean_Tully Contributor
    edited May 15

    You will have to either build out beast modes for each metric, or build them out as formulas in Magic ETL. In essence, you'll be making a bunch of formulas that look like the one below, which would be for sales, previous day, this year:

    sum(case when date_field = current_date() - 1 then sales_field end)

    Sales, previous day, last year: would be:

    sum(case when date_field = sub_date(current_date(), interval 1 year) - 1 then sales_field end)

    And the YOY change would be a new beast mode taking the difference between the two formulas. Repeat down the line for each date iteration, adapting the comparison between your date_field and the current_date() function to fit your needs.

    Hope this helps!

  • German_Rodriguez
    German_Rodriguez Member
    https://community-forums.domo.com/main/discussion/comment/95846#Comment_95846

    thank you for sharing, I was able to move a little forward but not to the end yet, hopefully, someone will be able to push me a little bit further,

    checking the Last Year part I was able to get

    1 Complete Current Week (LY)

    case when YEAR(Simple_Date) =YEAR(CURDATE()) -1 and WEEK(Simple_Date) = WEEK(CURDATE()) then TotalRevenue END

    1 Complete Current Month (LY)

    case when YEAR(Simple_Date) =YEAR(CURDATE()) -1 and MONTH(Simple_Date) = MONTH(CURDATE()) then TotalRevenue END

    1 Complete Current Quarter (LY)

    case when YEAR(Simple_Date) =YEAR(CURDATE()) -1 and QUARTER(Simple_Date) = QUARTER(CURDATE()) then TotalRevenue END

    1 Complete Current Year (LY)

    case when YEAR(Simple_Date) = YEAR(CURDATE()) -1 then TotalRevenue END

    pending to get Yesterday (LY)

    but I need to get the partial totals, for example, today (5-15-2024) the report needs to show for

    Previous Day (LY) [5-14-2023]

    WTD (LY) is the sum of the totals from dates [5-14-2023 to Previous Day (LY)]

    MTD (LY) is the sum of the totals from dates [5-01-2023 to Previous Day (LY)]

    QTD (LY) is the sum of the totals from dates [4-01-2023 to Previous Day (LY)]

    YTD (LY) is the sum of the totals from dates [1-01-2023 to Previous Day (LY)]

    i will continue sharing the updates until is complete

  • Sean_Tully
    Sean_Tully Contributor
    Answer ✓

    For MTD and WTD, you can use the dayofmonth() and dayofweek() functions to get the numerical value of the previous day in terms of week or month, and then compare to last year's to make sure the same value is less than or equal.

    QTD and YTD can get tricky depending on if you're using a special fiscal year or not. If it's just the straight calendar year, the dayofyear() function should work. If not, you'll have to make some adjustments in the beast mode. Someone else might know how to do QTD in an easy way, but I think you'd have to define each quarter in the beast mode and then adjust the value you get from the dayofyear() function accordingly.

  • German_Rodriguez
    German_Rodriguez Member

    it works, LY is working the way I need it

    2024-05-15_12-45-23.png

    the calculations are:

    Previous Day (LY)

    SUM(CASE WHEN DATE(Simple_Date) = DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY), INTERVAL 1 YEAR) THEN TotalRevenue END)

    WTD (LY)

    SUM(CASE
    WHEN YEAR(Simple_Date) = YEAR(CURRENT_DATE()) - 1
    AND WEEK(Simple_Date) = WEEK(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR))
    AND Simple_Date < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
    THEN TotalRevenue
    END)

    MTD (LY)

    SUM(CASE
    WHEN YEAR(Simple_Date) = YEAR(CURRENT_DATE()) - 1
    AND MONTH(Simple_Date) = MONTH(CURRENT_DATE())
    AND DAYOFMONTH(Simple_Date) < DAYOFMONTH(CURRENT_DATE())
    THEN TotalRevenue
    END)

    QTD (LY)

    SUM(CASE
    WHEN YEAR(Simple_Date) = YEAR(CURRENT_DATE()) - 1
    AND QUARTER(Simple_Date) = QUARTER(CURRENT_DATE())
    AND Simple_Date < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
    THEN TotalRevenue
    END)

    YTD (LY)
    SUM(CASE
    WHEN YEAR(Simple_Date) = YEAR(CURRENT_DATE()) - 1
    AND Simple_Date < DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)
    THEN TotalRevenue
    END)


    the next step is to calculate the difference between the Current Year and the Last Year

  • German_Rodriguez
    German_Rodriguez Member

    now, put all the totals together in a friendly way

    Capture.PNG

  • German_Rodriguez
    German_Rodriguez Member

    Final version, Working

    2024-05-16_09-37-52.png

Categories