YTD, YOY, M-1

Member
edited August 15

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?

Tagged:

• Coach

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

```
sum(
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.

** Did this solve your problem? Accept it as a solution! **

• 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.

** Did this solve your problem? Accept it as a solution! **

• Coach

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

** Did this solve your problem? Accept it as a solution! **

• 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?

• Coach

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

```
sum(