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

    Plotting a trend line for average

    kshitij.nagarkar

      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.

       

       

           

      MonthsPriceNumberPrice/NoPriceNumberPrice/No
      2017-092017-10
      11001010101224.6
      2100224.5101119.2
      3100128.3101128.4
      4100425101147.2
      5100128.3101156.7
      6100137.7101616.8
      7100156.7101911.2
      8100214.8NANANA
        • 1. Re: Plotting a trend line for average
          Yuriy Fal

          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