1 Reply Latest reply on Oct 10, 2018 2:22 PM by Yuriy Fal

Plotting a trend line for average

Hi Everyone,

My question is: that I want to plot a trend line for the average of Price/No.

My x-axis is Months

y-axis is Price/No

and on trend line i am showing the average of Price/No for all years like for 'Months=1 whats the average of Price/No for 2017-09 and 2017-10'.

I am able to plot this up easily but the problem is that in the last row there is NA for 2017-10 so for the below mentioned scenario I want average only till 'Months=7'.

I already have the average till Months=8 but for this case need average till Months=7 (any way to exclude this last month) ?

PS: Data is coming from database so there is not like NA. (I just put down the data in excel to explain the scenario)

Any help is appreciated.

 Months Price Number Price/No Price Number Price/No 2017-09 2017-10 1 100 10 10 101 22 4.6 2 100 22 4.5 101 11 9.2 3 100 12 8.3 101 12 8.4 4 100 4 25 101 14 7.2 5 100 12 8.3 101 15 6.7 6 100 13 7.7 101 6 16.8 7 100 15 6.7 101 9 11.2 8 100 21 4.8 NA NA NA
• 1. Re: Plotting a trend line for average

Hi Kshitij,

You may want to use an LOD calculation like this:

MONTH( [your_date] ) <= { MAX( MONTH( [your_date] ) ) }

either as a standalone Filter on a view (with the value of True selected)

or as a filter inside your AVG() calculation, something like this:

AVG(

IF MONTH( [your_date] ) <= { MAX( MONTH( [your_date] ) ) }

THEN [your_metric]

END

)

where [your_metric] should be either a datasource column or a Row-Level Calculation.

Yours,

Yuri