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

Member

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.

• Coach

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
```
**Did this solve your problem? Accept it as a solution!**
• 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 any users posts that helped you.
• 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 any users posts that helped you.
• 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:

```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 any users posts that helped you.
• 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 any users posts that helped you.
• 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 any users posts that helped you.