topic Re: Custom AVG Trend Line in QlikView

Custom AVG Trend Line

Fri, 22 Feb 2013 13:24:51 GMT

Does anybody know whick is the expression to make an custom AVG Trend Line in a graphic for example of the value Sum(Sales) and dimension CalendarMonthAndYear?

Thanks and happy weekend.

Re: Custom AVG Trend Line

Fri, 22 Feb 2013 13:45:40 GMT

Hi,

Please find the attached doc.

Hope it helps you.

Regards,

yaseen

Re: Custom AVG Trend Line

Fri, 22 Feb 2013 14:01:29 GMT

Sorry but I don't get it. The AVG line is in the bottom of the graphic and all of the bars are equal.

Did I miss anything?

Regards

Re: Custom AVG Trend Line

Fri, 22 Feb 2013 14:10:57 GMT

Hi,

My dats is having same values so the line is at the bottomn.

Please chk the doc now.

Regards,

Yaseen

Re: Custom AVG Trend Line

Fri, 22 Feb 2013 14:27:51 GMT

I still don't get it. Why do you use the percentage for the AVG? According to the data you use, the AVG line should be in the 110 value and parallel to the X axis. Do you agree?

Re: Custom AVG Trend Line

jagannalla — Fri, 22 Feb 2013 14:34:09 GMT

Hello,

Add your dimension as YearMonth & expression as =Sum(Val). Now at left button of expression tab, just check Average in TrendLInes.

Hope it helps you

Cheers!!

Jagan

Re: Custom AVG Trend Line

Fri, 22 Feb 2013 14:40:18 GMT

Thanks Jagan, I already knew that, the problem is that you can't format the prese trend lines. For example a dash line. That's why I want to make a new one by myself.

Thanks anyway

Re: Custom AVG Trend Line

jagannalla — Fri, 22 Feb 2013 16:10:16 GMT

I think you can go to presentation tab of chart -> Reference Lines -> Add -> in expression tab, =Avg(val)

Re: Custom AVG Trend Line

whiteline — Fri, 22 Feb 2013 17:25:24 GMT

Hi.

You could use total statement to calculate average value:

=Avg(total NEWVALUE)

Or a bit more complex if you have more than one value for each YEARMONTH:

=Avg(total aggr(Sum(NEWVALUE), YEARMONTH))

Re: Custom AVG Trend Line

Wed, 27 Feb 2013 07:57:49 GMT

The 2nd one works for me!! Thanks. The expression finally is:

Avg(total aggr(((SUM( {<Supplier={'PNA010031'}>} Purchase.Amount)*-1)/SUM(  {<Customer={'CNA008007','CNA008008'}>}Sales.Qty)), CalendarMonthAndYear))

Thanks a lot you all for your time