6 Replies Latest reply on Feb 19, 2017 4:23 PM by Madhusudhan Khambham

# Weekly average based on last 7 days

I have to create a chart based on weekly average of Calculation1 in attached report based on bank and weeks are defined by last 7 days from the run date e.g if report is run for 01/23/2017 then week will include from 23rd to 17th, 16th to 10th

Daily Data:

day                bank      Calculation1

01/23/17       Bank1       100

01/23/17       Bank2       200

01/20/17       Bank1       300

01/20/17       Bank2       700

.

.

.

Expected Results: (if run for 01/23/17)

Bank            Week1 Average(23rd to17th)          Week2 Average (16th to 10th)...................

Bank1          400                                                       300

Bank2          500                                                        450

.

.

.

.

• ###### 1. Re: Weekly average based on last 7 days

Nat Vats

Please find the attached workbook. Please refer sheet 4.

Is this what you are expecting?

Thanks

• ###### 2. Re: Weekly average based on last 7 days

Hi Nat,

Find my approach based on datepart-function below as reference and stored in attached workbook version 9.3 located in original threat.

Step 1: define week based on start week tuesday

Week: datepart('week', [Asofdate],'tuesday')

• ###### 3. Re: Weekly average based on last 7 days

Thanks for your response. In my case week end date is the date for which report is run and start date is (end date-7).

In your case your week starts at 22nd, 15th.

• ###### 4. Re: Weekly average based on last 7 days

Nat Vats

I believe this is  what you are expecting. Otherwise please share the expected result.

Thanks

1 of 1 people found this helpful
• ###### 5. Re: Weekly average based on last 7 days

Can you share the workbook?

• ###### 6. Re: Weekly average based on last 7 days

Hi Nat Vats

Good to hear that it satisfied your requirement.

Attached the workbook for your reference.

Thanks