Doubt with sum from today() to quarter

[#1]

Hi everybody

I have to make a quarterly average.

I have 4 quarters. I did this with

CEIL(Num(Month(FECHA))/3)&'Q'

AS QUARTER

Now with this i have Q1,Q2,Q3,Q4.

Ok i have to calculate the sum(money) from today() to the start of the quarter.

I mean, for example, we are in 25/02/2015. The start of the quarter is in 01/01/2015. Then i have to calculate 25/02/2015 - 01/01/2015 = 56 days

But if for example we'd be in 02/04/2015 it'd be 02/04/2015 - 01/04/2015 = 1 day

How i do this?

Thank you!

Greetings

[JonnyPoole]

This would work to sum up 'Amount' where 'Date' is between today and the quarter start.

= sum(  {<Date={">=$(=QuarterStart(Today()))<=$(=Today())"}>}  [Amount])

[Anonymous]

There is a function InQuarterToDate() just for your case:

sum(if(InQuarterToDate(Date, today(),0), money))

[#4]

Thank you! Now i have another doubt. I have to do the avg of this i mean the money between that two dates divided by the number of days

how i do this?

thank you!!!

[#5]

Thank you!

This function is working great

Now i have another doubt. I have to do the avg of this i mean the money between that two dates divided by the number of days

how i do this?

[JonnyPoole]

You can do date arithmetic for that. you may or may not need floor() function.

ex

sum(  {<Date={">=$(=QuarterStart(Today()))<=$(=Today())"}>}  [Amount])

/

(  floor(Today()) -  floor(QuarterStart(Today()))  + 1   )

[Anonymous · Accepted solution]

This should work:

sum(if(InQuarterToDate(Date, today(),0), money))

/

count(distinct if(InQuarterToDate(Date, today(),0), Date))

[#8]

Thank you

But i did this and it counts 24

count(distinct if(InQuarterToDate(FECHA, today(),0), FECHA))

It's counting from today to the start of the month

Greetings!

[Anonymous]

Maybe you don't have FECHA every day(?)  Try the option from Jonathan (I simplified a little):

sum(if(InQuarterToDate(Date, today(),0), money))

/

(Today()-QuarterStart(Today())+1)