trends ratio for last 4 months

Hiraayub
Hiraayub Member
in Charting

Hi,

I created this card last year and it was working fine but as the year has changed and I'm working with 4 months data so 2 are now in last year Nov & Dec and 2 Jan & Feb this year.

I tried working with Rank & Window function but running into problem with ASC and DESC

My second pic in Yellow is how I want the results.

Month & Year.JPG


Expected Results.JPG


Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I would change your beast mode to be like this:

    CASE WHEN DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY) = DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY) THEN 
'Current Month'
WHEN DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY) = DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY), INTERVAL 1 MONTH) THEN 
'Last Month'
WHEN DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY) = DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY), INTERVAL 2 MONTH) THEN 
'Month Prior'
WHEN DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY) = DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY), INTERVAL 3 MONTH) THEN 
'2 Months Prior'
END

    To break this down, I am using the date_sub and dayofmonth to be able to evaluate the dates as the first day of each month. In the 2nd, 3rd and 4th when statements, I am wrapping another date_sub around the current date to subtract additional months. This will allow you to handle the changing of years without breaking anything.

    **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

  • MarkSnodgrass
    MarkSnodgrass Coach

    I would try and sort on a single date field. If you don't already have it, and you just have the individual month and year columns, you can construct a date field like this in a formula tile.

    DATE(CONCAT(`Month`,'/1/',`Year`))

    Add this formula tile just before your rank and window tile and then sort on this date field descending. You can then add a row number or rank and then later filter to where it is 4 or less to get the most recent 4, if you need to limit it in your dataset.

    **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.
  • Hiraayub
    Hiraayub Member

    I do have a Date field, as not every products sells every day so the row # will be different.

    I'm don't think I understand the though process here with Rank.

  • MarkSnodgrass
    MarkSnodgrass Coach

    If you do have a date field, you can normalize a couple different ways to get them to be grouped by month and year. The easiest is probably using the Last_Day() function, like this (replacing dt with your fieldname that has the date:

    LAST_DAY(`dt`)

    This will create a date field for you and have all the transactions in a given month have the same date. You can then use this field in your rank and window and order by this field descending. Try that for starters before tackling the rank and see how it looks for you.

    **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.
  • Hiraayub
    Hiraayub Member

    Sorry, I'm working on too many things altogether and the thought process is scrambled.

    It's the Ratio Trend up that I'm trying to show on card,

    the Beast mode for that is


    CASE WHEN `Ratio % Last Month` > `Ratio % Month Prior` AND `Ratio % Month Prior` > `Ratio % 2 Month Prior`

    THEN 1

    END


    and this should be the result.

    Expected Results.JPG

    I was getting the Months by using the Beast mode, because of the year difference this is not working any more.


    CASE 

    WHEN `Month` = `Current Month` THEN 'Current Month'

    WHEN `Month` = `Current Month` - 1 THEN 'Last Month'

    WHEN `Month` = `Current Month` - 2 THEN 'Month Prior'

    WHEN `Month` = `Current Month` - 3 THEN '2 Month Prior'

    END

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    I would change your beast mode to be like this:

    CASE WHEN DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY) = DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY) THEN 
'Current Month'
WHEN DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY) = DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY), INTERVAL 1 MONTH) THEN 
'Last Month'
WHEN DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY) = DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY), INTERVAL 2 MONTH) THEN 
'Month Prior'
WHEN DATE_SUB(`dt`, INTERVAL (DAYOFMONTH(`dt`) - 1) DAY) = DATE_SUB(DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY), INTERVAL 3 MONTH) THEN 
'2 Months Prior'
END

    To break this down, I am using the date_sub and dayofmonth to be able to evaluate the dates as the first day of each month. In the 2nd, 3rd and 4th when statements, I am wrapping another date_sub around the current date to subtract additional months. This will allow you to handle the changing of years without breaking anything.

    **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.
  • Hiraayub
    Hiraayub Member

    Thank you!

Categories