Need Help Changing to LAST 12 Months and sorting in proper order

Goodeman
Goodeman Member
in Cards, Dashboards, Stories

The Card below shows the total call volume by month, and I have a quick filter to modify the number of years.

image.png

I created the following beast modes.

Standard Date (X Axis)

DATE_ADD(addTimestamp,
INTERVAL
(YEAR(CURRENT_DATE()) - YEAR((addTimestamp)))
year)

Count of 'Addtimestamp' y Axis

Reported Years for the Series ( Filtered

YEAR(addTimestamp)

Number of Years - (With Quick filter so I can choose the number of years)

YEAR(CURRENT_DATE()) - YEAR(addTimestamp)

image.png

QUESTION: I need to change this to the last 12 months but still maintain the years and PUT THE MONTHS IN ORDER of LAST 12 MONTHS. Like this:

image.png

Comments

  • DavidChurchman
    DavidChurchman Coach
    edited February 11

    I think you could create a BeastMode for your sort:

    MOD(

    MONTH(`addTimeStamp`)+12 - month(current_date()),

    12

    )

    Add 12 to your month and subtract the current month. Then use modulo 12. If the current month is March:

    • March + 12 - March = 12, Modulo 12 = 0
    • April + 12 - March = 13, Modulo 12 = 1
    • …
    • January + 12 - March = 10, Modulo 12 = 10
    • February + 12 - March = 11, Modulo 12 = 11

    This would only work with "graph-by" months, but I think if you added in a second sort on DAY(`addTimeStamp`), it should be pretty robust

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • Goodeman
    Goodeman Member

    Thanks @DavidChurchman that worked!

Categories