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

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?



Best Answers

  • MarkSnodgrass
    Answer ✓

    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.

    **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.
  • MarkSnodgrass
    Answer ✓

    @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
     ))
    
    **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.
  • MarkSnodgrass
    Answer ✓

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

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

    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.

    **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.
  • NathanDorsch
    NathanDorsch Member
    edited March 2022

    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?

  • 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

      ))

  • 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.

    **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.
  • MarkSnodgrass
    Answer ✓

    @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
     ))
    
    **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.
  • @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
    Answer ✓

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

    **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.
  • How do I accept? My clicking on Yes to the "Did this answer the question?"

  • @NathanDorsch Yep! That did it! Thanks!

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