4 Replies Latest reply on Nov 29, 2016 10:13 PM by Shinichiro Murakami

how to get average of last 6 data points and only display one value?

Below table shows for each revenue variance, what the corresponding last 7 weeks of average is for that week.

I am able to write the formula to get the average of the last n values from each of my data but i only want to show the average for the latest value (week 9).

So for week 9, the revenue variance is 0.15. Its average value is equal to (0.15+0+0.17+0.02+0.1+0.14+0)/7) = 0.0829.

Week 9 is the current week as you can see, the rest of the revenue variance from week 10 onwards are 0.

How do i show just the current week and its average value without showing all the other weeks values?

I tried editing the week in the filter but that caused my average value to change.

the calculated field i used were:

index for average = last() ---> of which i select 0.

Avg = WINDOW_AVG(sum([Revenue Variance]),-6,0) Thanks,

jenn

• 1. Re: how to get average of last 6 data points and only display one value?

Jenn

Not sure this is same question with this How to create limit condition on aggregated value in filter card?  or not, anyways same thing, could you attach packaged workbook?

Thanks,

Shin

• 2. Re: how to get average of last 6 data points and only display one value?

Its a different question as both use the value 6 :D Same workbook though. For this question, please refer to worksheet 1 and 3. worksheet 1 is what i want ultimately but i use a hack now to get that value. worksheet 3 is the true value.

• 3. Re: how to get average of last 6 data points and only display one value?

attached package workbook

• 4. Re: how to get average of last 6 data points and only display one value?

Jenn

Thank you for attaching a packaged workbook.

For this filtering only the latest week, create calculated field like below as filter.

[The latest week]

window_min(if sum([Revenue Actual])>0 then last() END)

= (if sum([Revenue Actual])>0 then last() END)

Then show only "True" in filtering card.

See "Filter" Tab

Thanks,

Shin

1 of 1 people found this helpful