topic Re: Average per month over a date range trend line in QlikView

Average per month over a date range trend line

Fri, 08 Aug 2014 23:57:02 GMT

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.

Re: Average per month over a date range trend line

ecolomer — Sat, 09 Aug 2014 03:40:52 GMT

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

Re: Average per month over a date range trend line

MK_QSL — Sat, 09 Aug 2014 06:09:51 GMT

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

Re: Average per month over a date range trend line

MarcoWedel — Sat, 09 Aug 2014 22:27:15 GMT

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

Re: Average per month over a date range trend line

Mon, 11 Aug 2014 19:21:19 GMT

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!