Grouping 'Last Connection Time' by month

Coni
Coni Member
in Beast Mode

Looking to create a Beast mode that will break MAX(`Verizon_Last_Connection_Date`) into chunks by quarter. Example below.

I am able to validate the calculation, however it does not seem to work when I add this formula to the series. It just displays '1 month' and 'over year'.

CASE
WHEN DATEDIFF(CURRENT_DATE(), MAX(Verizon_Last_Connection_Date)) <=30 THEN '1 Month'
WHEN DATEDIFF(CURRENT_DATE(), MAX(Verizon_Last_Connection_Date)) <=90 THEN '2-3 Month'
WHEN DATEDIFF(CURRENT_DATE(), MAX(Verizon_Last_Connection_Date)) <=180 THEN '4-6 Month'
WHEN DATEDIFF(CURRENT_DATE(), MAX(Verizon_Last_Connection_Date)) <=270 THEN '6-9 Month'
ELSE 'Over Year'
END

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I would check to see what you have in your sorting properties. This can throw off how Domo groups your data which will affect the MAX.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • Coni
    Coni Member

    Update:

    There are ` around the Verizon_Last_Connection_Date. Somehow they did not copy over properly.

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I would check to see what you have in your sorting properties. This can throw off how Domo groups your data which will affect the MAX.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    How is your data being grouped? Are you looking for the max date across all records or within a specific partition of your data?

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

Categories