topic Re: Average value for Quarter - Dynamic in Dashboards & Analytics Discussions

Average value for Quarter - Dynamic

nikhil_cr — Fri, 01 Dec 2023 12:42:23 GMT

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.

Re: Average value for Quarter - Dynamic

BrandonR — Fri, 01 Dec 2023 17:22:13 GMT

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

Re: Average value for Quarter - Dynamic

JoeM — Fri, 08 Dec 2023 14:30:46 GMT

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

Re: Average value for Quarter - Dynamic

Spd_03 — Sat, 09 Dec 2023 12:26:15 GMT

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.

Re: Average value for Quarter - Dynamic

anurag — Mon, 11 Dec 2023 14:43:33 GMT

Hi,

Can you use table resultset to prepare the data (construct a Quarter column from Month) and then use the solution proposed by @BrandonR ?

Re: Average value for Quarter - Dynamic

BrandonR — Mon, 11 Dec 2023 20:32:05 GMT

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

Re: Average value for Quarter - Dynamic

Spd_03 — Wed, 13 Dec 2023 14:04:21 GMT

Hi @BrandonR,

Even we've tried this way but it's not working.