Programs Ending in the Next 60 Days

ARutledge

Hello Everyone, I'm trying to create a card that will show what programs we're running will end in the next 60 days. I think a beast mode would help accomplish my goal but I'm unsure of what to do. Each program has multiple billing dates associated with it so I need it to be based off of just the latest billing date.

MarkSnodgrass

Hard to say for sure without seeing your data, but something like this might work:

CASE WHEN DATEDIFF(MAX(billingdate),CURRENT_DATE()) < 60 THEN 'Program Ending'

ELSE 'All good'

END

You can then drag this into your filters and filter to Program Ending. Your table card would need to have your program name and then your billing date field and set to Max.

ARutledge

Hi Mark! Thank you for your reply. I tried this and it didn't seem to work.

I used the Beastmode you created above:

CASE WHEN DATEDIFF(MAX(Billing Month New),CURRENT_DATE()) < 60 THEN 'Program Ending'
ELSE 'All good'
END

I dragged it into filters and filtered for program ending. I have program names in my rows and I set the billing date field to max (date range is all time) but it's not filtering for dates from now +60 days. There's a bunch of old dates showing up. I attached a screen shot.

Thank you for the help!

DOMO Screenshot.png

MarkSnodgrass

To get rid of dates that are older than today, you would want to modify this statement to look like this:

CASE WHEN DATEDIFF(MAX(Billing Month New),CURRENT_DATE()) >=0 
AND DATEDIFF(MAX(Billing Month New),CURRENT_DATE()) < 60 
THEN 'Program Ending' 
ELSE 'All good' 
END

ARutledge

Thank you so much! That worked perfectly. But now I have another question. I tried to add a couple more rows to my report (sales region and sales client) but I'm getting this error:

"An issue has occurred during processing. We are unable to complete the request at this time - 'undefined'"

There is no error when I take out those 2 rows.

Screenshot 2024-11-20 121130.png

ARutledge

Never mind! I think it was because I put the fields after the date field. I was able to make it work when I moved them. Thank you again for all the help!

MarkSnodgrass

Hard to say for sure without seeing your data, but something like this might work:

CASE WHEN DATEDIFF(MAX(billingdate),CURRENT_DATE()) < 60 THEN 'Program Ending'

ELSE 'All good'

END

You can then drag this into your filters and filter to Program Ending. Your table card would need to have your program name and then your billing date field and set to Max.

ARutledge

Hi Mark! Thank you for your reply. I tried this and it didn't seem to work.

I used the Beastmode you created above:

CASE WHEN DATEDIFF(MAX(Billing Month New),CURRENT_DATE()) < 60 THEN 'Program Ending'
ELSE 'All good'
END

I dragged it into filters and filtered for program ending. I have program names in my rows and I set the billing date field to max (date range is all time) but it's not filtering for dates from now +60 days. There's a bunch of old dates showing up. I attached a screen shot.

Thank you for the help!

DOMO Screenshot.png

MarkSnodgrass

To get rid of dates that are older than today, you would want to modify this statement to look like this:

CASE WHEN DATEDIFF(MAX(Billing Month New),CURRENT_DATE()) >=0 
AND DATEDIFF(MAX(Billing Month New),CURRENT_DATE()) < 60 
THEN 'Program Ending' 
ELSE 'All good' 
END

ARutledge

Thank you so much! That worked perfectly. But now I have another question. I tried to add a couple more rows to my report (sales region and sales client) but I'm getting this error:

"An issue has occurred during processing. We are unable to complete the request at this time - 'undefined'"

There is no error when I take out those 2 rows.

Screenshot 2024-11-20 121130.png

ARutledge

Never mind! I think it was because I put the fields after the date field. I was able to make it work when I moved them. Thank you again for all the help!