trends ratio for last 4 months

Hiraayub

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.

MarkSnodgrass

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.

MarkSnodgrass

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.

Hiraayub

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

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.

Hiraayub

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.

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

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.

Hiraayub

Thank you!