0

Script with summation for a given range date?

Hi guys!

How can I get, inside a script, the summation for a given range date?

 

This is my case:

1. I have a table “INCOME” with all my records for invoices. There is a “TOTAL” field

2. I have another table “Taxes” where I need to calculate the summation of “TOTAL” by “Cumulative Quarters” (not “Individual Quarters”)

 

I mean:

For the first quarter is easy:

 

let QUART1 := YEAR + " " + Q1;

sum((select INCOME where yearquarter(Date) = QUART1).TOTAL)

 

The problem come where I need to calculate the summation from:

January 1 to June 30 (this is Q1+Q2)

 

And also for:

January 1 to September 30 (this is Q1+Q2+Q3)

 

How could I resolve these code for range dates in a smart way?

(for the “last cumulative quarter” is also easy: it's the entire year… no problem)

 

Thanks for your support! :-)

1 reply

null
    • Etérea Estudios
    • Cristobal
    • 4 yrs ago
    • Reported - view

    Just after writing my question I have found this post:

    http://bit.ly/2uCn957

    And thanks to it I have finally found a clever solution for my case:

     

    For Quarter 1+2:

    let From := date('Year', 1, 1);

    let To := date('Year', 6, 30);

    sum((select INCOME where Date >= From and Date <= To).TOTAL)

     

    For Quarter 1+2+3:

    let From := date('Year', 1, 1);

    let To := date('Year', 9, 30);

    sum((select INCOME where Date >= From and Date <= To).TOTAL)

     

    Solved! :-)

     

    (I LOVE NINOX!)

Login to reply

Content aside

  • 4 yrs agoLast active
  • 1Replies
  • 1212Views