4 Replies Latest reply on Oct 18, 2016 5:51 AM by Jason Olson

# Average over Q1, Q1+Q2, Q1+Q2+Q3

Hi,

I have a calculated filed "Average time till payment". Now i want to make a view where i can show Monthly data wil average of (First 3 months, 6 months and 9 months).

What i need finally is like this. Quarterly average is easy using analysis tab, but i dnot know how to calcualte Avg(6months) and Avg(9months).

Payment TypeJanFebMarAvgQ1AprMayJunAvgQ2Avg(6months)JulAugSepAvgQ3Avg(9months)octnovDecAvgQ4Avg(Year)
A
B
C

Any help?

• ###### 1. Re: Average over Q1, Q1+Q2, Q1+Q2+Q3

i love this and at the same time i need some one to lead me through so that i can become an expert

• ###### 2. Re: Average over Q1, Q1+Q2, Q1+Q2+Q3

This is not the prettiest approach but it does the job in one table. Take a look at the attached sample and let me know if I misunderstood any of the requirements at all because I'm sure I can tweak it. In short the solution leverages table calculations along with the DATEPART function.

1 of 1 people found this helpful
• ###### 3. Re: Average over Q1, Q1+Q2, Q1+Q2+Q3

Hi Jason Olson,

Thanks for you soulution, It is very nice and helpful.

Do you think it will work fine if we have no values for some months?

Exp. If we have avg time till payment is 6days for Jan, 4 for Feb and there is no payment for month of Mar. Then the average time till payment for this Quarter should be 5Days.

• ###### 4. Re: Average over Q1, Q1+Q2, Q1+Q2+Q3

That would require a slight tweak but nothing too serious. I have attached an updated file showing this. The key parts to look at are the Q1 - Q4 Count measures and then the updates to all of the Avg columns.