Record count for prior month or prior quarter at start of the year

Randyb

I have a Beast Mode that I've been using that was working fine up until January 1st. The issue is that I have a number of Beast Modes that are calculating record counts for Prior Month, Prior Quarter and Prior 2 Quarters past.

They all seem to have trouble getting past the 2018 component, which I know I've coded in the Beast Mode because otherwise it was pulling quarter and month info for numerous years instead of the current year.

I assume that somehow I need to build in a case statment that checks to see what the year month is and based on that will alter the Beast Mode.

Here is a sample of what I'm using for Prior Month Beast Mode, the current Quarter -1 and Current Quarter -2 are very similar.

Let me know what I should do or if this is possible.

thanks

Randy

 

Sum(CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
+
CASE WHEN `hireDate` <= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day ) AND `dateOfTermination` >= DATE_ADD(CONCAT(MONTH(CURRENT_DATE()),'-1-',YEAR(CURRENT_DATE())), interval -1 day )
Then 1 else 0
End)

RGranada

Hi,

 

Can you try this one out:

 

Sum(CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH)  AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
+
CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH) AND `dateOfTermination` >= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH)
THEN 1 ELSE 0 END)

 

This code: DATE_SUB(LAST_DAY(CURDATE()), interval -1 MONTH)  will give you the last day of the previous month.

 

Tell me how it goes.

RGranada

Hi,

 

Can you try this one out:

 

Sum(CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH)  AND `dateOfTermination`IS NULL THEN 1 ELSE 0 END
+
CASE WHEN `hireDate` <= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH) AND `dateOfTermination` >= DATE_SUB(LAST_DAY(CURDATE()), interval 1 MONTH)
THEN 1 ELSE 0 END)

 

This code: DATE_SUB(LAST_DAY(CURDATE()), interval -1 MONTH)  will give you the last day of the previous month.

 

Tell me how it goes.

Randyb

I tried it but it still didn't work, it produce the same values as the current month and current quarter which tells me its still not breaking out of 2018.

That is the issue I'm having is that right now everything is returning 01 2018 as the month year and won't go back to 12 2017 for the prior month and won't go back 10 2017 etc for the prior Quarters.

Once I'm far enough into 2018 everything would be fine again but for the next 6 months my current approach isn't going to work.

Let me know if you have any other ideas.

Thanks for you help

Randy

RGranada

Hi

 

Maybe your problem is not on the BeastMode I tried that in my account and it's returning the date 2017/12/31 for the current date.

 

Are your "hiredate" and "dateOfTermination" DateTime fields?

 

Regards,

Randyb

The date of termination is a date time field however the time value doesn't change it is always 18:00

I'm digging into the results detail and doing a comparison using hard coded dates to validate one more time, it may actually be working.

Stay tuned.

Randy

 

 

Randyb

I've got a bouple of other issues going on that I discovered but I think this is working, thanks for your help

Randy

user055174

Hi, I have tested the proposed solution

DATE_SUB(LAST_DAY(CURDATE()), interval -1 MONTH)

and it breaks for February, it says 29th of January is no longer in the "last month", but in the "current month".

So I am using this formula, instead

CASE 

WHEN DATE(`Payment Date`) > SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()) DAY) THEN 'This Month' 

  WHEN DATE(`Payment Date`) < SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()-1) DAY) 

   AND DATE(`Payment Date`) > SUBDATE(SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()) DAY), interval DAYOFMONTH(SUBDATE(CURRENT_DATE(), interval DAYOFMONTH(CURRENT_DATE()) DAY)) DAY) THEN 'Last Month' 

  ELSE 'Not current or previous month'

END

GrantSmith

Hi @user055174

You could also utilize the last_day function to make it a bit simpler. LAST_DAY just returns the last day in the month for a given date.

CASE 
WHEN LAST_DAY(`dt`) = LAST_DAY(CURRENT_DATE()) THEN 'This Month'
WHEN LAST_DAY(`dt`) = LAST_DAY(DATE_SUB(CURRENT_DATE(), interval 1 month)) THEN 'Last Month'
WHEN LAST_DAY(`dt`) = LAST_DAY(DATE_ADD(CURRENT_DATE(), interval 1 month)) THEN 'Next Month'
ELSE 'Other Month'
END