Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Average per month over a date range trend line

Hello, I have a chart below. Expression is sum(ord_totalvolume).  Primary dimension is date(ord_startdate,'DD-MMM') and secondary dimension is month. Simple enough. Is it possible to display average trend line per month like below but only for that particular month? For example dark green trend line should start at the begging of Jun and stop at the end of Jun, peach colored trend line should start at the beginning of July and end at the end of July etc (instead of running across all selected months). I have tried a number of different aggr, sum, avg function combinations for the secondary expression with no luck. Thanks in advance.

1 Solution

Accepted Solutions
MVP

Hi Tanja,

I tested a solution with two seperate overlapping charts.

The chart on top has set transparency to 100% and the average per month as expression:

=Sum(TOTAL<Month> ord_totalvolume)/Count(DISTINCT TOTAL<Month> ord_startdate)

If there are no other, more straight forward solutions, maybe this helps.

regards

Marco

4 Replies
Master II

You can chage the expression to Sum by month and select average per each expresion (each month)

MVP

Would like to try if you can provide some sample data...

MVP

Hi Tanja,

I tested a solution with two seperate overlapping charts.

The chart on top has set transparency to 100% and the average per month as expression:

=Sum(TOTAL<Month> ord_totalvolume)/Count(DISTINCT TOTAL<Month> ord_startdate)

If there are no other, more straight forward solutions, maybe this helps.

regards

Marco

Not applicable
Author

Enrique Colomer - I'm afraid I don't understand what you mean - do I need to hard code names of each month for a total of 12 additional expressions? Please explain/provide an example.  Formula provided by Marco Wedel "=Sum(TOTAL<Month> ord_totalvolume)/Count(DISTINCT TOTAL<Month> ord_startdate)" is exactly what I need but it looks like it can't be combined with another expression since it 'jumbles' up the graph...

Manish Kachia - you could use the file provided by Marco Wedel

Marco Wedel - your suggested solution is nothing short from genius! Formula works great for what I need and overlapping graphs with static min, max and step make it so the graphs are exactly the same size. Though I would prefer a straight forward solution - this will work great too. Attached is the result. Thank you!

Community Browser