Selling Days Remaining in Month

Reply
Highlighted
Green Belt

Selling Days Remaining in Month

I need to write a Beast Mode where I take the maximum number of days in the month and subtract days passed, not including today.

 

To accomplish this, I have:

 

DATEDIFF(MAX(`AppointmentDate`),NOW() - 1)

The challenge is that I want to count only selling days only.

Monday - Friday = 1 selling day

Saturday = 0.5 selling day

Sundday = 0

 

Any thoughts on how to accomplish this?


Accepted Solutions
Green Belt

Re: Selling Days Remaining in Month

I was able to solve this using the following beast mode:

 

SUM(
CASE
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) >= '2016-12-16' AND DATE(`Date`) <= '2016-12-31' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-11-24' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-09-05' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-07-04' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-05-30' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 1 THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 2 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 3 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 4 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 5 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 6 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 7 THEN 0.5
END)

View solution in original post


All Replies
Purple Belt

Re: Selling Days Remaining in Month

CZ, I have done several business day calcs, but to do it, I built a date calendar table that labels days as a business day or not. Currently, DOMO does not have a business date function. So my table has items like: Current Date Business Date (0 or 1) Business day of month (1 to 20 - or 19 or 23 depending on number of bus days in month) Business day of quarter Business day of year You get the idea.
Dojo Community Member
** Please like responses by clicking on the thumbs up
** Please Accept / check the answer that solved your problem / answered your question.
Green Belt

Re: Selling Days Remaining in Month

I'm trying to accomplish two things:

 

Revenue Goal for current month / Selling Days in current month, which I have successfully done

 

and

 

Revenue Goal for current month / Remaining selling days in month, which I'm still struggling with. I have added a DAYNAME column and assigned them values in another column called 'Day of Week Value' to the datset. However, I'm still not at a final solution yet.

 

Thoughts?

Moderator

Re: Selling Days Remaining in Month

@Shevy, can you provide further insight for czmudzinski?

Green Belt

Re: Selling Days Remaining in Month

I was able to solve this using the following beast mode:

 

SUM(
CASE
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) >= '2016-12-16' AND DATE(`Date`) <= '2016-12-31' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-11-24' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-09-05' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-07-04' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DATE(`Date`) = '2016-05-30' THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 1 THEN 0
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 2 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 3 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 4 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 5 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 6 THEN 1
WHEN DATE(`Date`) < DATE(NOW()) AND DAYOFWEEK(`Date`) = 7 THEN 0.5
END)

View solution in original post

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!