Sum of Value based on specific date parameters

Randyb
Randyb Member
in Magic ETL

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

Best Answer

  • RGranada
    RGranada Contributor
    Answer ✓

    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.

     

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.

Answers

  • Chronos
    Chronos Member

    Is anyone able to help out with this request?

  • RGranada
    RGranada Contributor
    Answer ✓

    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.

     

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • Randyb
    Randyb Member

    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

Categories