11 Replies Latest reply on Nov 30, 2016 8:02 AM by Shinichiro Murakami

how to get average value of last 3 values only?

i want to get the average of the last 3 values of my time series data. However it sums everything up and did not get the average when i used window_average()

my data:

 date value 1/1/16 10 2/1/16 20 3/1/16 30 4/1/16 40 5/1/16 50

my function:

WINDOW_AVG(sum([Value]),-3,0)

The output value i should get is (50+40+30)/3 = 40

• 1. Re: how to get average value of last 3 values only?

1. Window_avg should be -2 to 0, not 3 to 0

2. create calculated field of "index"

[index]

last()

3. Change [index] to discrete and put in Filter shelf

4. Filter index only choosing "0".

Thanks,

Shin

• 2. Re: how to get average value of last 3 values only?

Hi Jenn,

You can also refer to below thread for the same.

-Ashish

• 3. Re: how to get average value of last 3 values only?

Sorry what if my data had 3 more rows of zeros that i want to omit and only start from the last 3 where data is greater than 0.01?

 date value 1/1/16 10 2/1/16 20 3/1/16 30 4/1/16 40 5/1/16 50 6/1/16 0.0 7/1/16 0.0 8/1/16 0.0

So like before, the avg of 'last' 3 will be (50+40+30)/3=40

I want to omit values where value<0.01.

i put value in the filters where value >0.01 but i think the average i want is still wrong.

• 4. Re: how to get average value of last 3 values only?

Hi Jenn,

Try putting below field in the filter section and seelect true.

Filter

SUM([Value])>0.01

-Ashish

• 5. Re: how to get average value of last 3 values only?

[Date only valid]

if [Value]>0.01 then [Date] end

Put above field instead of [Date]

Shin

• 6. Re: how to get average value of last 3 values only?

I tried it on my data set where values are in decimals and i have 2 more columns that i filter on. the window_average did not work. it ended up just filtering to index=0 which is the last item but not doing any average.

Any idea why my other filters are affecting it?

my dataset:

1 date,value,fiscal_yr,fiscal_quarter

2 1/1/15,0.100,2015,4

3 2/1/15,0.200,2015,4

4 3/1/16,0.300,2016,1

5 4/1/16,0.400,2016,1

6 5/1/16,0.500,2016,1

7 6/1/16,0.000,2016,1

8 7/1/16,0.000,2016,1

9 8/1/16,0.000,2016,1

• 7. Re: how to get average value of last 3 values only?

I know that table calculation is always troublesome.

Which does not mean necessarily bad, because that means we have "flexibilities" when we use table calculations.

Only one action needed to make it work.

magically

Thanks,

Shin

• 8. Re: how to get average value of last 3 values only?

Thanks, this help me to get the avg. 3 latest month sales. But now I'm having a new challenge.

I want to get my level inventory using total inventory / avg. last 3 month sale. Is that posible?.. For example I have 100 units inventory and my average sales are 50, so I have 2 months of inventory.

Sorry I'm new with this.

• 9. Re: how to get average value of last 3 values only?

Hi Alejandro

Data

[How many months?]

sum([Inventory])/[Avg last 3 months]

Thanks,

Shin

1 of 1 people found this helpful
• 10. Re: how to get average value of last 3 values only?

Shin, thank you very much. It worked.. I was doing it without the SUM formula.

• 11. Re: how to get average value of last 3 values only?

Alejandro

You are very welcome.

Could you mark my answer as correct?

Thanks,

Shin

1 of 1 people found this helpful