Period over Period (MOM/YOY) option in Multi-Value card for 2 months prior

NathanDorsch

I am building a dashboard that only shows completed months' worth of data. A few of the datasets I have though provide lagging data.... so for their specific cards, I just want to show data up until the prior month.

I have created multi-value cards to show the MOM and YOY trends... they work when I have a completed dataset (as shown below), but I can't figure out how to get this to work for comparing two months ago (so today for a card pulling from a dataset that has incomplete data for February, I'd want to compare January '22 to December '21 and January '21). Is there a way to do this?

MarkSnodgrass

On the cards where you want to show only data from prior months you could create a beast mode that you would put in your filters. It might look like this:

CASE WHEN `dt` < DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY) THEN 'Prior'
ELSE 'Current'
END

Replace dt with your fieldname that has the date.

You would then put this beast mode in your filter and set the filter to Prior. This will hide any data in the current month.

MarkSnodgrass

@NathanDorsch with your concatenation:

Date(concat(
case when Month(`Date`) > 1 then Year(`Date`)
when Month(`Date`) = 1 then Year(Date) - 1
end,
'1',
case when Month(`Date`) > 1 then Month(`Date`) - 1 
when Month(`Date`) = 1 then 12
end
  ))

A date of today would return 202213. The date() function is going to have a hard time converting that to a date.

If you want to go this route, I would change it to:

Date(concat(
  
case when Month(`Date`) > 1 then Month(`Date`) - 1 

when Month(`Date`) = 1 then 12

end
 ,

'/1/',

case when Month(`Date`) > 1 then Year(`Date`)

when Month(`Date`) = 1 then Year(Date) - 1

end
 ))

MarkSnodgrass

@NathanDorsch glad I was able to help. If you can accept any answers that helped you, that will help others in the community.

MarkSnodgrass

On the cards where you want to show only data from prior months you could create a beast mode that you would put in your filters. It might look like this:

CASE WHEN `dt` < DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY) THEN 'Prior'
ELSE 'Current'
END

Replace dt with your fieldname that has the date.

You would then put this beast mode in your filter and set the filter to Prior. This will hide any data in the current month.

NathanDorsch

hmmm... that didn't quite work. I'm thinking what I could do though is create a beast mode that simply subtracts one month from each date... or better yet (since not all months have the same number of days), can I create a beastmode with the prior month to each date? I could then use this in the Time Period.

So if the date is February 27, 2022, it will return January 2022. How can I do that?

NathanDorsch

I tried this and it doesn't seem to be working... What might I be doing wrong here>?

Date(concat(

case when Month(`Date`) > 1 then Year(`Date`)

when Month(`Date`) = 1 then Year(Date) - 1

end,

'1',

case when Month(`Date`) > 1 then Month(`Date`) - 1 

when Month(`Date`) = 1 then 12

end

  ))

MarkSnodgrass

You can use the DATE_SUB function to subtract different intervals at a time, such as month.

DATE_SUB

Subtracts date or datetime values (as intervals) to date values in a date column.

DATE_SUB('DateCol', interval 1 month)

In this example, if the value for DateCol was 2/27/2022, it would subtract 1 month and result in 1/27/2022. Now, if you needed to get to the last day of the month if the month you just subtracted to, you can wrap the LAST_DAY() function around the Date_Sub function. This would make 1/27/2022 end up being 1/31/2022.

LAST_DAY(DATE_SUB('DateCol', interval 1 month))

Let me break down a part of my first example in case that might help you as well.

DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY)

There are several functions wrapped together here.

CURRENT_DATE() is returning today's date whenever the card is viewed.

DAYOFMONTH(CURRENT_DATE()) - 1) is returning the day of today's date and reducing that by 1. So, if today is the 3rd, then this would return 2. This number is then used in the DATE_SUB function to subtract the number of days from today's date.

The result of the entire function returns the first day of the current month. So, right now, it would return 3/1/2022.

MarkSnodgrass

@NathanDorsch with your concatenation:

Date(concat(
case when Month(`Date`) > 1 then Year(`Date`)
when Month(`Date`) = 1 then Year(Date) - 1
end,
'1',
case when Month(`Date`) > 1 then Month(`Date`) - 1 
when Month(`Date`) = 1 then 12
end
  ))

A date of today would return 202213. The date() function is going to have a hard time converting that to a date.

If you want to go this route, I would change it to:

Date(concat(
  
case when Month(`Date`) > 1 then Month(`Date`) - 1 

when Month(`Date`) = 1 then 12

end
 ,

'/1/',

case when Month(`Date`) > 1 then Year(`Date`)

when Month(`Date`) = 1 then Year(Date) - 1

end
 ))

NathanDorsch

@MarkSnodgrass and @GrantSmith You guys are killing it! Thank you.

I'll take your correction of my formula, as that appears to work. I was strugglign to follow on the other suggestion... but probably my own mental limitation 😏

MarkSnodgrass

@NathanDorsch glad I was able to help. If you can accept any answers that helped you, that will help others in the community.

NathanDorsch

How do I accept? My clicking on Yes to the "Did this answer the question?"

MarkSnodgrass

@NathanDorsch Yep! That did it! Thanks!