Average Trendline

[mhmmd_srf]

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.

[sunny_talwar]

Does this look right?

[mhmmd_srf]

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

[sunny_talwar]

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

[mhmmd_srf]

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

[sunny_talwar]

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)))

[mhmmd_srf]

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

[sunny_talwar]

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?

[mhmmd_srf]

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

[mhmmd_srf]

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