Beast mode that would put people in a specific group based upon a date

Golfoholic
Golfoholic
in Charting

I have a dataset that shows tenants that are late on their payments. Some our in Auction status, and it has an auction date. What I am trying to do is put these people that are in Auction status in to 3 groups. Those that are 30, 60 , and 90 days past that auction date.

Then Ideally, would be to use this in a pivot table where you can see those in each group you would click in to.

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @user048760

    I don't know how you're calculating the difference for days past the auction but you can use something like this and replace `days past` with your formula or column that holds the number of days past.

    CASE WHEN `days past` >= 90 THEN '90+'
WHEN `days past` >= 60 then '60-89'
WHEN `days past` >= 30 THEN '30-59'
ELSE 'not past'
END
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach

    I would create a beast mode that divides them into groups like this:

    CASE WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 90 THEN '90 days late'
WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 60 THEN '60 days late'
WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 30 THEN '30 days late'
ELSE 'Not Late'
END

    Replace 'dt' with the name of your auction date field.

    You can then add this field to a pivot table and also to your filters to filter out the not late group if you don't want them to show.

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

    @GrantSmith We don't have that number calculated. What I am trying to see is from the Auction date that we do have. How many fit in those groups between that auction date and today.

  • MarkSnodgrass
    MarkSnodgrass Coach

    @user048760 using the datediff function and the current date function in my initial response would get you that day difference.

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

    @MarkSnodgrass Adding on to this. That worked like a dream. Now would it be possible to add a group of future dates to the same beast mode so they all appear in a single column? So could i add another 2 groups where the Auction Date is 30-59, and +60 days in the future? So looking at everything that is out a month or more.

  • MarkSnodgrass
    MarkSnodgrass Coach

    Yes, you could do this a couple different ways, depending on how you want to look at your formula. I might go with this:

    CASE WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 90 THEN '90 days late'
WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 60 THEN '60 days late'
WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 30 THEN '30 days late'
WHEN DATEDIFF(`dt`,CURRENT_DATE()) >= 90 THEN '90 days future'
WHEN DATEDIFF(`dt`,CURRENT_DATE()) >= 60 THEN '60 days future'
WHEN DATEDIFF(`dt`,CURRENT_DATE()) >= 30 THEN '30 days future'
ELSE 'Not Late'
END

    Notice in the new 3 statements that I added, I switched which fields is the first parameter and which is the second. This will allow you to look at future dates and it would return a positive number when evaluating.

    You might want to test by just creating a beast mode that only has the datediff function in it and view the results in a table card. Once you see the results of that, it will make it easier to properly build out your case statement.

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

    @MarkSnodgrass Last twist to it. Now what I am wanting to do is break these out so that i could have a count for each in said group so for those with in 30 days late and those that have a date with in the coming 30 days. then so on so forth for the other groups. 60 days to 31 late 31 to 60 days upcoming. PL&TY!!!! I would expect this to be several individual beastmodes. The plan is to have each one of these groups be displayed in a bar chart to show those totals.

  • MarkSnodgrass
    MarkSnodgrass Coach

    You should be able to drag this beast mode into the x-axis and then also to the y-axis and then on the y-axis field, click the pencil icon to change the aggregation to count. Did you try doing that?

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

    @MarkSnodgrass Ah ok, that did work! Just to be sure i'm on the same page looking at these groups, lets take 30 days late - would this take those that are 30 days late or less or would it take those that are between 30 days and 59 days late?

  • MarkSnodgrass
    MarkSnodgrass Coach

    It would take those that are between 30-59 days late.

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

Categories