Cumulative Average and Sales for months

[vikas_nandanwar]

Hi,

I have below set of data consisting of Month and Inv

I want expression for Avg Inv(Calculated for reference as expected answer)

I want my month graph to show my avg inventory as highlighted in Avg Inv (Expression for)

Month	Inv	Avg Inv	Expression for
1-Jan-16	64.97	64.97	Avg of Jan
1-Feb-16	73.17	69.07	Avg of Jan, Feb
1-Mar-16	77.48	71.87	Avg of Jan, Feb, Mar
1-Apr-16	71.56	71.8	Avg of Jan, Feb, Mar, Apr
1-May-16	73.51	72.14	Avg of Jan, Feb,  Mar, Apr, May
1-Jun-16	73.65	72.39	Avg of Jan, Feb,  Mar, Apr, May, Jun
1-Jul-16	79.23	74.77	Avg of Feb, Mar, Apr, May, Jun, Jul
1-Aug-16	78.96	75.73	Avg of Mar, Apr, May, Jun, Jul, Aug
1-Sep-16	79.73	76.11	Avg of Apr, May, Jun, Jul, Aug, Sep
1-Oct-16	81.72	77.8	Avg of May, Jun, Jul, Aug, Sep, Oct
1-Nov-16	76.03	78.22	Avg of Jun, Jul, Aug, Sep, Oct, Nov
1-Dec-16	75.46	78.52	Avg of Jul, Aug, Sep, Oct, Nov, Dec
1-Jan-17	70.63	77.09	Avg of , Aug, Sep, Oct, Nov, Dec, Jan
1-Feb-17	76.12	76.62	Avg of , Sep, Oct, Nov, Dec, Jan, Feb
1-Mar-17	77.76	76.29	Avg of Oct, Nov, Dec, Jan, Feb, Mar
1-Apr-17	72	74.67	Avg of Nov, Dec, Jan, Feb, Mar, Apr
1-May-17	71.68	73.94	Avg of Dec, Jan, Feb, Mar, Apr, May
1-Jun-17	72.54	73.46	Avg of Jan, Feb, Mar, Apr, May, Jun
1-Jul-17	81.46	75.26	Avg of Feb, Mar, Apr, May, Jun, Jul
1-Aug-17	77.13	75.43	Avg of Mar, Apr, May, Jun, Jul, Aug
1-Sep-17	79.94	75.79	Avg of Apr, May, Jun, Jul, Aug, Sep
1-Oct-17	80.22	77.16	Avg of May, Jun, Jul, Aug, Sep, Oct
1-Nov-17	76.94	78.04	Avg of Jun, Jul, Aug, Sep, Oct, Nov
1-Dec-17	76.04	78.62	Avg of Jul, Aug, Sep, Oct, Nov, Dec

[petter]

You can use an expression with RangeAvg() and Above() like this:

The last column is your pre-calculated rolling average just to see that the expression RangeAvg(Above(TOTAL (Inv),0,If(RowNo()<6,RowNo(),6))) calculates to the same value.

[vikas_nandanwar]

Thanks this worked.

[vikas_nandanwar]

Hi,

this works fine with the month sorted and the given sample data.

But when i use it with the auto calender date, it do not work

i.e.

RangeAvg(Above(TOTAL (Inv),0,If(RowNo()<6,RowNo(),6)))

But when my data is at a transaction date level, it donot work

please help

[petter · Accepted solution]

Can you add TOTAL to both the RowNo() functions like this:

RangeAvg(Above(TOTAL (Inv),0,If(RowNo(TOTAL)<6,RowNo(TOTAL),6)))

That might be enough to make it work.

What are the dimensions you are using and what type of chart are you using this in? Straight Table or Pivot Table?

[vikas_nandanwar]

Thanks

This worked