Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead 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.

ChartExample.jpg

1 Solution

Accepted Solutions
MarcoWedel

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:

QlikCommunity_Thread_128871_Pic1.JPG.jpg

=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

View solution in original post

4 Replies
ecolomer
Master II
Master II

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

MK_QSL
MVP
MVP

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

MarcoWedel

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:

QlikCommunity_Thread_128871_Pic1.JPG.jpg

=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!

qlikCommunity_Thread_128871_Result.jpg