Skip to content

    • Charting

    Charting

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

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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Answers

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

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

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

      1. CASE WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 90 THEN '90 days late'
      2. WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 60 THEN '60 days late'
      3. WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 30 THEN '30 days late'
      4. ELSE 'Not Late'
      5. 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.
    • Member

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

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

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

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

      1. CASE WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 90 THEN '90 days late'
      2. WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 60 THEN '60 days late'
      3. WHEN DATEDIFF(CURRENT_DATE(),`dt`) >= 30 THEN '30 days late'
      4. WHEN DATEDIFF(`dt`,CURRENT_DATE()) >= 90 THEN '90 days future'
      5. WHEN DATEDIFF(`dt`,CURRENT_DATE()) >= 60 THEN '60 days future'
      6. WHEN DATEDIFF(`dt`,CURRENT_DATE()) >= 30 THEN '30 days future'
      7. ELSE 'Not Late'
      8. 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.
    • Member

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

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

      @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?

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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories