11 Replies Latest reply on Mar 1, 2017 7:16 AM by Shinichiro Murakami

# Weekly Average for last 5 business days and monthly average for last 30 business days

I have attached a sample worksheet. In this worksheet dates are hardcoded for last 2 days average. I do not want to hardcode but calculate average based on last 5 business days(Weekdays) and monthly average based on last 30 business days.

• ###### 1. Re: Weekly Average for last 5 business days and monthly average for last 30 business days

Hi Nat

Let me clarify.

Do you have all dates' data including weekend and want to filter out weekend, or  you only have weekday data and only filter last 3 line items/ last 30 line item from the data ?

Thanks,

Shin

• ###### 2. Re: Weekly Average for last 5 business days and monthly average for last 30 business days

I have weekday data only.

• ###### 3. Re: Weekly Average for last 5 business days and monthly average for last 30 business days

In that case, you can use index and moving average.

[Last Three Days AVG]

window_avg(sum([Deposits]),-2,0)

[Last 30 Days AVG]

window_avg(sum([Deposits]),-29,0)

Thanks,

Shin

• ###### 4. Re: Weekly Average for last 5 business days and monthly average for last 30 business days

Problem with this is that I need to show just one day of data in report which includes 30 days and last 5 days average. As soon as I apply a filter on date to run only for 01/23 these 2 average fields values also disappear.

Date : 01/23 (report is run for this date)

Bank1     Product1       Deposit     Last 5 business days average    Last 30 business days average

Bank1     Product1       400

• ###### 5. Re: Weekly Average for last 5 business days and monthly average for last 30 business days

Here you go.

[Date lookup filter]

lookup(min([Date]),0)

Thanks,

Shin

1 of 1 people found this helpful
• ###### 6. Re: Weekly Average for last 5 business days and monthly average for last 30 business days

Thanks. That worked. My last question is I have a Report Date parameter which user will input to run the report. How do I assign the value of input parameter value to the Date lookup filter in this report

• ###### 7. Re: Weekly Average for last 5 business days and monthly average for last 30 business days

I'm sorry I don't understand the question very well.

Please clarify your frequentest with some illustration.  What is user selection what is expected result.

And are you using today() or any free form dates  etcetc...

Thanks,

Shin

• ###### 8. Re: Weekly Average for last 5 business days and monthly average for last 30 business days

I have attached the sample worksheet. I just need to add  one input parameter (Report date) and it can be a freeform input date field where user can input the date and it should populate the data for that report date. Basically Date lookup filter should be assigned the same date entered in this parameter.

• ###### 9. Re: Weekly Average for last 5 business days and monthly average for last 30 business days

Here is one idea.

[Filter with Param]

if [ReportDate]=attr([Date]) then index() end

Thanks,

Shin

1 of 1 people found this helpful
• ###### 10. Re: Weekly Average for last 5 business days and monthly average for last 30 business days

Worked. Thanks a lot for your help.

• ###### 11. Re: Weekly Average for last 5 business days and monthly average for last 30 business days

Great! and could you mark my answer as correct to close the thread.

Thanks,

Shin