Average value for Quarter - Dynamic

[nikhil_cr]

Hi, I'm trying to calculate avg value for each quarter based on the current Month completed.

I have the below table and I'm trying to calculate the Avg value for each Quarter.

Avg Revenue for Quarter Q1 = (1+2+3)/3

Avg Revenue for Quarter Q2 = (4+5+6)/3 and So on....

Let us assume we are in November, then the avg revenue should be - 
Quarter Revenue Average Q4 = (10+11)/2 (diving by 2 here because December is still not completed)

How can I make this divisor dynamic based on the months completed for each quarter.

 Appreciate your help, thanks.

[BrandonR]

Hello,

Incorta's built in average method allows you to group by a portion of your dataset within it as long as your insight is already grouping along those lines. Using this, you should be able to average the revenue while grouping by quarter. Keep in mind this only works if you have already aggregated up to the month level. See screenshots attached. Since you are grouping by another "flag" field and the average method counts the rows within the groups for you, you shouldn't have any issue if you do not have rows for some months out of a quarter.

If you have any other question, let me know,

Brandon

[JoeM]

@nikhil_cr  - Did @BrandonR 's reponse solve your question?

[Spd_03]

Hi @BrandonR  & @JoeM ,

Thank you for your inputs, this method will work only if the Quarter Column is present in the table by default.

Since we used the case statement to construct a Quarter column from Month, the value is averaged by month rather than by quarter.
Can you please help us to resolve this issue.

[BrandonR]

Hello,

You should be able to use a formula in place of a dedicated quarter column in the groupBy so long as your insight also groups by the same formula.

Brandon