YTD vs Year Prior

zuchu
zuchu Member
in Magic ETL

Hi guys,

Looked in other posts here but couldn't find the exact answer. I'm creating a table card that would show YTD vs Year prior (Bar charts are not an option since comparing many different categories. The best I could find to show Year prior is

SUM(CASE WHEN YEAR(DocDate)= YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND month(DocDate) <= month(CURDATE()) THEN DocTotal_Contribution ELSE 0 END)

The issue with that is that is is summarizing all data from 2023 including all month of April. I want to compare data from time Jan 01 2023 till today (April 18 of 2023). Vs Year to Date (Jan 01 2024 vs today)

To calculate YTD I use the following formula and is working just fine.

SUM(CASE WHEN YEAR(DocDate) = YEAR(CURDATE()) THEN DocTotal_Contribution ELSE 0 END)

How can I add the extra layer to count also the current day in the year prior?

Thanks

Answers

  • ArborRose
    ArborRose Coach

    That's the formula I use. The version you show is not summing the full year. That month condition means it is summing YTD (year to date)…..less than or equal to the current month. You need to put some more criteria to it for the period you want. I am about to go to a meeting, I'll try to give you something specific when I can.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ArborRose
    ArborRose Coach

    The condition "<= month(CURDATE())" means compare years up until today's date. It is comparing Jan through today (April 18, 2024). For specific ranges like a specific month:

    To include records where the month is April -

    SUM(CASE WHEN YEAR(DocDate) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))
    AND MONTH(DocDate) = 4
    THEN DocTotal_Contribution
    ELSE 0 END)


    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ArborRose
    ArborRose Coach

    Oh…make it less than but not equal if you want up to today but not including today.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • zuchu
    zuchu Member

    @ArborRose thank for your help. I tried your syntaxes but they all seem to be including full April for 2023.

    This syntax

    SUM(CASE WHEN YEAR(DocDate)= YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND month(DocDate) <= MONTH(CURDATE()) THen DocTotal_Contribution ELSE 0 END)

    still returns data for 2023 including full month of April. I think the logic in the syntax above includes all data for year prior PRIOR to the end of April 2023. Any way to change this logic to show only until 18? It will be a live card so I don't want to use specific numbers so it the card the update with time.

  • zuchu
    zuchu Member

    @ArborRose this is the formula I just used and it returns what I need

    SUM(CASE WHEN DocDate <= DATE_SUB(CURDATE(),INTERVAL 1 YEAR) AND DocDate >= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 YEAR), '%Y-01-01')
    THEN DocTotal_Contribution END)

    Thanks

Categories