YTD, YOY, M-1

Kanokkarn

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?

ArborRose

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.

ArborRose

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.

ArborRose

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

Kanokkarn

@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

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.