YTD, YOY, M-1

Kanokkarn
Kanokkarn Member
edited August 15 in Beast Mode

Hi All,

I want to create a column for YTD, YOY comparison by month and M-1.

Example output is below: I have datetime in a format at yyyy-mm-dd

Can someone show a use case for this?

image.png

Tagged:

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓

    To compare month to a previous year's same month:


    
      sum(
    
        case when YEAR(`transaction_entry_date`) = YEAR(DATE_ADD(CURRENT_DATE(),-365)) 
    
             and MONTH(`transaction_entry_date`) = MONTH(DATE_ADD(CURRENT_DATE(),-365)) 
    
             and `date` <= DATE_ADD(CURRENT_DATE(),-365)
    
        then `amount` else 0 end

    And add a criteria such as Month(date)=1 if you need to specify a month like January.

    For a percentage, I create a formula to find the difference of CY and PY. That's just subtracting one from the other. Then use that as the numerator. So the percent would be something like CY-PY/PY.

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

Answers

  • ArborRose
    ArborRose Coach
    edited August 15

    If you use formulas for dynamic years in an ETL using the Group By Tile, such as:

    current year:

    sum(
    case when YEAR(date) = YEAR(CURRENT_DATE())
    and date <= CURRENT_DATE()
    and sku='Product A'
    then amount else 0 end
    )

    previous year:

    sum(
    case when YEAR(date) = YEAR(DATE_ADD(CURRENT_DATE(),-365))
    and date <= DATE_ADD(CURRENT_DATE(),-365)
    and sku='Product A'
    then amount else 0 end
    )

    previous 2 year:

    sum(
    case when YEAR(date) = YEAR(DATE_ADD(CURRENT_DATE(),-730))
    and date <= DATE_ADD(CURRENT_DATE(),-730)
    and sku='Product A'
    then amount else 0 end
    )

    You can also use YEAR(`date`)=YEAR(CUR_DATE())-1. On the current year you don't need the second line. On previous years you do - to compare YTD to previous years YTD.

    You can put it into a grouped bar chart with the property set to include the data table. Use an integer value for month to place on the sort to get the months to display in the proper order.

    image.png

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

  • ArborRose
    ArborRose Coach

    Oh…and add the condition of MONTH in the same way we do YEAR to get Month over Month.

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

  • Kanokkarn
    Kanokkarn Member

    @ArborRose Hi thank you for helping

    but I also want to see %YOY growth too

    For ie. Jan 2024 compare to Jan 2023 = %YOY on Jan

    And so on.. %YOY on Feb

    Do you know how to do this?

  • ArborRose
    ArborRose Coach
    Answer ✓

    To compare month to a previous year's same month:


    
      sum(
    
        case when YEAR(`transaction_entry_date`) = YEAR(DATE_ADD(CURRENT_DATE(),-365)) 
    
             and MONTH(`transaction_entry_date`) = MONTH(DATE_ADD(CURRENT_DATE(),-365)) 
    
             and `date` <= DATE_ADD(CURRENT_DATE(),-365)
    
        then `amount` else 0 end

    And add a criteria such as Month(date)=1 if you need to specify a month like January.

    For a percentage, I create a formula to find the difference of CY and PY. That's just subtracting one from the other. Then use that as the numerator. So the percent would be something like CY-PY/PY.

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

Categories