How to create YTD, LYTD, LYLW, etc columns from a FISCAL CALENDAR

Mickey

I have a dataset that has sales per item going by each week (yyyyww) for the last three years, only I can't use things like YEAR(CURDATE()) or YEARWEEK(CURDATE()) because the yearweek we're getting from the dataset is based on a fiscal calendar (Ex: The first full week of august is the first week of the year, so July 29, 2018 is the first of the 2018 fiscal calendar, whereas Aug 4, 2019 is the first week of 2019).

I'm trying to create Retail - TY, Retail - LYTD, Retail - LW, Retail - LYLW columns either via Beast Mode or ETL Transform, but SQL and coding really isn't my strong suit. I need to be able to compare these values to the each other (Retail - TY to Retail - LYTD) to get a percentage, something like in the below image, and I need them to not be locked to a date since they'll have to keep rotating to the next week/year.

I was almost successful by attempting to use the OVER () function on a CASE basis, comparing a week to the entire dataset's MAX week, but that ultimately failed since it was grouping the data by year and wouldn't allow me to compare the values to each other even if the year column wasn't on the card at all. I could have also just been using it wrong

Oh and variables won't work as this will need to be published to a publication, and variables do not transfer.

I understand this is a big ask but I'd love to figure out how to keep doing this in the future. Any help is HUGELY appreciated!

ArborRose

I create a formula for current year (CY) and previous year (PY).

Do this and you can then look at the difference. Let's say I want this year quarter to date. And previous year quarter to date.

CASE WHEN YEAR(`date`) = YEAR(CURRENT_DATE()) 

     AND QUARTER(`date`) = QUARTER(CURRENT_DATE()) 

THEN `amount` 

ELSE 0 

END



CASE WHEN YEAR(`date`) = YEAR(CURRENT_DATE()) - 1 

     AND QUARTER(`date`) = QUARTER(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))

     AND `date` < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

THEN `amount` 

ELSE 0 

END

You can then use a multi-value columns chart type to create a text card or gauge with the two values.

In this case, I would get

Production
$000.00
CY vs PY
{upper/down arrow} 00%

So apply that to your issue. You have different formulas you can create with your own date ranges or criteria. Use a filter on the card if you are showing a specific category or include the category in the formula.

ArborRose

I create a formula for current year (CY) and previous year (PY).

Do this and you can then look at the difference. Let's say I want this year quarter to date. And previous year quarter to date.

CASE WHEN YEAR(`date`) = YEAR(CURRENT_DATE()) 

     AND QUARTER(`date`) = QUARTER(CURRENT_DATE()) 

THEN `amount` 

ELSE 0 

END



CASE WHEN YEAR(`date`) = YEAR(CURRENT_DATE()) - 1 

     AND QUARTER(`date`) = QUARTER(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))

     AND `date` < DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

THEN `amount` 

ELSE 0 

END

You can then use a multi-value columns chart type to create a text card or gauge with the two values.

In this case, I would get

Production
$000.00
CY vs PY
{upper/down arrow} 00%

So apply that to your issue. You have different formulas you can create with your own date ranges or criteria. Use a filter on the card if you are showing a specific category or include the category in the formula.