Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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
Highlighted

Re: Average per month over a date range trend line

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
Highlighted
Honored Contributor II

Re: Average per month over a date range trend line

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

Highlighted
MVP
MVP

Re: Average per month over a date range trend line

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

Highlighted

Re: Average per month over a date range trend line

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

Highlighted
Not applicable

Re: Average per month over a date range trend line

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