Previous Month Last Year

KristinDavis
KristinDavis Member
in Charting

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?

Tagged:

Best Answer

  • brycec
    brycec Contributor
    Answer ✓

    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)

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!

Answers

  • brycec
    brycec Contributor
    Answer ✓

    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)

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!

  • KristinDavis
    KristinDavis Member

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

  • GrantSmith
    GrantSmith Coach

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

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

    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
    brycec Contributor

    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.

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!

  • KristinDavis
    KristinDavis Member

    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.

    Screenshot 2024-04-04 at 2.55.59 PM.png

  • KristinDavis
    KristinDavis Member

    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:

    Screenshot 2024-04-08 at 11.09.48 PM.png

    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.

Categories