topic Re: Average Trendline in QlikView

Average Trendline

mhmmd_srf — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have a chart where Bar is showing Dollar valuer per month. And in Line we need show the Trendline Average.

If I use the Trendlines property it is simply dividing by 12. Instead of that I need Sum(Dollar) / Available Month in Current Year.

Is there any other property to do the same.

Thanks,

Sarif

I am attaching the Sample App.

Re: Average Trendline

sunny_talwar — Mon, 12 Jun 2017 12:46:06 GMT

Does this look right?

Re: Average Trendline

mhmmd_srf — Tue, 13 Jun 2017 07:05:42 GMT

Hello Sunny... not exactly... This doing Sum(Dollar) for CY/12, instead of that I want Sum(Dollar) for CY/8.

As my dashboard is showing 8 months for 2017.

Please help.

Thanks,

Sarif

Re: Average Trendline

sunny_talwar — Tue, 13 Jun 2017 07:25:44 GMT

For your current selection, what is the average number you are expecting to see (provide a numeric number)?

Re: Average Trendline

mhmmd_srf — Tue, 13 Jun 2017 07:53:29 GMT

Avg should be 104042436.5 for 2017 selection.

832339492/8= 104042436.5

next month it should be divided by 9.

Thanks for your help.

Thanks,

Sarif

Re: Average Trendline

sunny_talwar — Tue, 13 Jun 2017 07:59:35 GMT

So, then why did you think that selecting Average Trend Line was not working? To me it seems to be working (bottom chart). Also, added an expression (above chart), but would not recommend using it (unless you have to for ignoring selections reason, in which case the below expression will have to be modified)

=If(sum(WIPYieldLoss) > 0, Avg(TOTAL Aggr(If(sum(WIPYieldLoss) > 0, Sum({<CstFiscalYear = {'2017'}>}WIPYieldLoss)), CstFiscalPeriod)))

Re: Average Trendline

mhmmd_srf — Tue, 13 Jun 2017 08:11:39 GMT

Average Trendline will always give total value / 12, right?

Fot Prior Year, it is ok.. but for Current Year we need No month should be actual.

Is my understanding correct?

Thanks,

Sarif

Re: Average Trendline

sunny_talwar — Tue, 13 Jun 2017 08:14:22 GMT

From what I am seeing, it is not... and in general it only gives the average based on what ever is shown on the chart. Right now there are 8 points, so it should show average based on 8 points only. The line is above 100,000,000.... doesn't that seems to be doing the right calculation?

Re: Average Trendline

mhmmd_srf — Tue, 13 Jun 2017 09:01:35 GMT

Hello Sunny,

I am using below script:

=(If(sum(WIPYieldLoss) > 0, Sum(TOTAL Aggr(Sum({<CstFiscalYear = {'$(v_CurrentFiscalYear)'}>}WIPYieldLoss), CstFiscalPeriod))))/

(count(distinct Left(DateCompleted_Key,6)))

But the division part is not working. Instead of that if I use 8, it is working. And

count(distinct Left(DateCompleted_Key,6)) is also separately showing 8 in Text box.

Am I missing anything?

Please help.

Thanks,

Sarif

Re: Average Trendline

mhmmd_srf — Tue, 13 Jun 2017 10:54:14 GMT

Hello Sunny,

I just applied your logic for CY and PY. It is coming correct when I select Year = 2017.

But I want two line should be constant if I select Year = 2016 also.

In the attached App, if I select 2016, then PY is not geting changed. But CY is changing.

I want both of them should be fixed.

Please help.

Thanks,

Sarif

Re: Average Trendline

sunny_talwar — Tue, 13 Jun 2017 11:00:17 GMT

Try this may be

=If(sum(WIPYieldLoss) > 0, Avg({<CstFiscalYear = {'2017'}>}TOTAL Aggr(If(sum({<CstFiscalYear = {'2017'}>}WIPYieldLoss) > 0, Sum({<CstFiscalYear = {'2017'}>}WIPYieldLoss)), CstFiscalPeriod)))

Re: Average Trendline

mhmmd_srf — Tue, 13 Jun 2017 11:05:10 GMT

Thanks a lot Sunny...it is working..

I need one more small help from you..

Could you please make me understand the syntax.

It would be really helpfull for me.

Thanks,

Sarif

Re: Average Trendline

sunny_talwar — Tue, 13 Jun 2017 11:09:39 GMT

Which expression do you need help with? and also is there a specific thing you don't understand in that expression?

Re: Average Trendline

mhmmd_srf — Tue, 13 Jun 2017 11:17:00 GMT

For Year = 2017 , selection,

In chart it is showing 8 months only. Then how PY is making division by 12 and Cy is making division by 8?

This is major confusion.

Thanks,

Sarif

Re: Average Trendline

sunny_talwar — Tue, 13 Jun 2017 11:21:54 GMT

Because PY is for previous years where there are 12 entries... and CY only have 8 entries... I guess Avg of 8 numbers will be Sum/8 and Avg of 12 will be Sum/12, isn't it?

Re: Average Trendline

mhmmd_srf — Tue, 13 Jun 2017 11:26:41 GMT

okk.. but one thing... if I select :

Year = 2017 and Group Major = Not Available:

Then chart is showing 7 Months. And how the line is behaving?

For CY, it is neither dividing by 7 nor 8,

For PY, it is neither dividing by 12 nor 7.

I am not getting the behaviour..

Please check.

Thanks,

Sarif

Re: Average Trendline

sunny_talwar — Tue, 13 Jun 2017 11:44:14 GMT

Dividing by 6 because there are 6 bars... 6 numbers.... Sum/6

Month 4 is missing

Re: Average Trendline

mhmmd_srf — Tue, 13 Jun 2017 12:01:21 GMT

Okk.. I understand for CY. Could you please let me know the behaviour of PY?

Please help.

Thanks,

Sarif

Re: Average Trendline

sunny_talwar — Tue, 13 Jun 2017 12:06:30 GMT

There seems to be 9 months using this

=Count( DISTINCT{<CstFiscalYear = {'2016'}>}CstFiscalPeriod)

So, 6871552.9059/9 = 763505.88

Changed the PY Expression to this

=If(sum(WIPYieldLoss) > 0, Avg({<CstFiscalYear = {'2016'}>}TOTAL Aggr(Sum({<CstFiscalYear = {'2016'}>}WIPYieldLoss), CstFiscalPeriod)))

Re: Average Trendline

mhmmd_srf — Tue, 13 Jun 2017 12:18:27 GMT

Here is my question, why 9 months.

If we select 2016 and Not Available, I can see there are 7 months.

Could you please check once?

Thanks,

Sarif