7 Replies Latest reply on Sep 20, 2018 6:32 AM by Shinichiro Murakami

# Calculate periods when combining weekly and bi-weekly data

For a report I have to calculate the difference between the value of the last period (week) and the period before.

This was no problem when we collected data on a weekly basis, since then I used weekdiff and showed the latest 5 weeks and the difference between them.

However, since recently I get bi-weekly data (we only measure once in the two weeks). Therefore, I cannot use weekdiff anymore, since then the latest 5 weeks have some missing data.

I added a short example with the weeknumbers.

Ideally instead of weekdiff I would use another calculation that would not count actual weeks but weeks we have measured, so that it does not skip weeks.

For example if we have data of week 42, the data of week 40 is one measurement period before and not 2 periods (weeks).

Any idea how I can solve this?
Thanks!

• ###### 2. Re: Calculate periods when combining weekly and bi-weekly data

Hi,

The difference between the periods should be 0,1,2,3,4 instead of 0,2,4,5,6.

So even though week 42 is 2 weeks apart from week 40, the difference in measurement periods is 1 instead of 2. Do you get what I mean?

• ###### 4. Re: Calculate periods when combining weekly and bi-weekly data

The goal is to be able to calculate the differences between the last X number of periods, so Im afraid that wont work right?

Before I used weekdiff to decide on the number of periods --> e.g. 5 weeks. However since we have bi-weekly measurements I need to show the last 5 periods and the difference between those.

So the difference in period can be one week if we measure weekly but two weeks if we measure bi-weekly.

• ###### 5. Re: Calculate periods when combining weekly and bi-weekly data

Still not cleat.

Could you clarify your expected result / View with using existing table and number example.

Thanks,

Shin

.

• ###### 6. Re: Calculate periods when combining weekly and bi-weekly data

Alright hereby another example:

For this graph i use the average value of the last period which is in de data and I compare this value with the value of the period before (diff. with previous period).

I now calculated last period and previous period with the weekdiff and monthdiff function (if weekdiff =0 then its the last period and if weekdiff = 1 its the period before for example). The user can change if they want to see periods in weeks or months.

However, now we measure bi-weekly, i get no value for the diff with previous period (weekdiff =1 vs weekdiff = 0), because the previous data is from week 35 2018 and not 36 2018. So I have to make sure that it does not look at actual weeks but the previous period in my data or something.

Should look sort of like the right image.

Does this help? • ###### 7. Re: Calculate periods when combining weekly and bi-weekly data

Unfortunately, not yet.

Could you provide the sample workbook which is currently (previously) working with reflecting this statement.

The user can change if they want to see periods in weeks or months.

And  are you trying to cover

1. only" bi-week or the

2. period which include combined rule of "bi-weekly" and "weekly"

1. means WW2, WW4, WW6, WW8, WW10

2. means WW4, WW5, WW6 (change) WW8  WW10,

Thanks,

Shin