Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
This should work:
sum(if(InQuarterToDate(Date, today(),0), money))
/
count(distinct if(InQuarterToDate(Date, today(),0), Date))
This would work to sum up 'Amount' where 'Date' is between today and the quarter start.
= sum( {<Date={">=$(=QuarterStart(Today()))<=$(=Today())"}>} [Amount])
There is a function InQuarterToDate() just for your case:
sum(if(InQuarterToDate(Date, today(),0), money))
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!!!
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?
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 )
This should work:
sum(if(InQuarterToDate(Date, today(),0), money))
/
count(distinct if(InQuarterToDate(Date, today(),0), Date))
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!
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)