Sum of Value based on specific date parameters

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

Find more posts tagged with

Accepted answers

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.

All comments

Chronos

Is anyone able to help out with this request?

RGranada

Hi,

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

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