Sum of Value based on specific date parameters

Randyb

I have a Card that is in Table view and I'm trying to populate multiple columns that are all summing a value based on specific date parameters. The issue I'm having is trying to set the parameters to autopopulate based on Current date.

For example I want the sum of flagged values which match certain date parameters, at the start of the year I use the following Beastmode and it returns the correct number:

 

Sum(Case when (`hireDate`) <='01/01/2017' And `dateOfTermination`is null
Then 1 else 0
End)
+
Sum(Case when (`hireDate`)<='01/01/2017' And `dateOfTermination`>='01/01/2017'
Then 1 else 0
End)

 

Now, if i want to see what this number is at the end of the quarter, two quarters prior from Current Date I can't get the result to return correctly and this is what I need to figure out. Again, if I hard code the date such as the following:

 

SUM(Case when (`hireDate`) <='06/30/2017' And `dateOfTermination`is null
Then 1 else 0
End
+
Case when (`hireDate`)<='06/30/2017' And `dateOfTermination`>='06/30/2017'
Then 1 else 0
End)

 

What can i input into the Beast Mode so that it can take the current Quarter Year which would be 42017 and subtract 2 quarters from that which would make it 22017 and have the calculations work.

Any insight would be greatly appreciated

Thanks

Randy

RGranada

Hi,

 

If I correctly understood your problem this modification should help you:

 

SUM(Case when (`hireDate`) <=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 2 QUARTER) And `dateOfTermination`is null
Then 1 else 0
End
+
Case when (`hireDate`)<=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 2 QUARTER) And `dateOfTermination`>=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 2 QUARTER)
Then 1 else 0
End)

 

I replaced your date with a formula that gives you the last day of the current quarter minus 2 quarters. 

 

Tell me how it goes.

 

Hope this helps.

 

Chronos

Is anyone able to help out with this request?

RGranada

Hi,

 

If I correctly understood your problem this modification should help you:

 

SUM(Case when (`hireDate`) <=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 2 QUARTER) And `dateOfTermination`is null
Then 1 else 0
End
+
Case when (`hireDate`)<=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 2 QUARTER) And `dateOfTermination`>=DATE_SUB(LAST_DAY(STR_TO_DATE(CONCAT(YEAR(CURDATE()),'/',QUARTER(CURDATE())*3,'/01'),'%Y,%m,%d')), interval 2 QUARTER)
Then 1 else 0
End)

 

I replaced your date with a formula that gives you the last day of the current quarter minus 2 quarters. 

 

Tell me how it goes.

 

Hope this helps.

 

Randyb

Thanks for your help, I knew it must be possible and I was on the right track with the Beast Mode I was trying to build but that was getting a bit advanced for me.

Really appreciate it.

Randy