Previous Month Last Year

KristinDavis

I'm trying to make some companion comparison cards so that I can display, for example, on one card the top 10 PDFs downloaded last month this year versus another card with the same month last year. I have the data by month already, and I need to have this for each month, moving forward.

Is there an easy way to set some cards to always look at the previous month last year?

brycec

You could use a YoY chart to show them like this on the same chart. Or, you can use a Beast Mode to filter to that date only, like:

`(CASE WHEN DATE_FORMAT(Date, '%Y-%m) = DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH), '%Y-%m') THEN 'Yes' ELSE 'No' END)

brycec

You could use a YoY chart to show them like this on the same chart. Or, you can use a Beast Mode to filter to that date only, like:

`(CASE WHEN DATE_FORMAT(Date, '%Y-%m) = DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 13 MONTH), '%Y-%m') THEN 'Yes' ELSE 'No' END)

KristinDavis

Where, then do I use that Beast Mode? In the Date Range field?

GrantSmith

Add the beast mode to the filter section of your card and then filter for 'Yes'.

KristinDavis

I'm still a little stuck. The date format is actually already calculated in the dataset to the first day of every month, and is in format like Jan 1, 2023.

brycec

Are you saying your date is stored as a string/text column and not of date data type? If so, you'll need to use the function STR_TO_DATE() to convert the column to a date first, then you can use the other Beast Mode I sent.

KristinDavis

No it's in a date format, it just looks like the date format in the formula is different than the format in my dataset. Which, I think, make the formula much simpler. I just need to tell it to take the date we selected in the card and subtract 13 months.

KristinDavis

Okay, I'm getting closer. That formula isn't quite working, so what I've done is this:

DATE_SUB(Date, INTERVAL 12 MONTH)

I'm getting this as my filter:

Which would be fine if I were just running this for March, because the way I've done Date is to simply use the first day of the month each month. However I'm trying to automate this report so it will send every month for the previous month, so setting the exact date of 2023-03-01 won't work on an ongoing basis.

When I try something more complex, like

CASE
WHEN DATE_SUB(Date, INTERVAL 12 MONTH) THEN 'Y'
ELSE 'N'
END

I do get 'Y' as the option and select it, but the data itself is still only showing March of 2024, not March of 2023.

So we're really close…. I'm missing… something.