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

Golfoholic

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.

GrantSmith

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

MarkSnodgrass

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.

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

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

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

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.

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

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?

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

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