Calculation of the # of week within a quarter

building reports webintelligence
1

Hello BoB- members,

I do have new challenging formula where I will need your help.
Actually I am looking for the number of the week in a quarter of a specific date.
I am not looking for the calendar week; I am interested in the 1st week, 2nd week … of a quarter.

Examples:
1st of July: 1
8th of July: 2
1st of Oct: 1
7th of Oct: 2

Can someone help to create such a formula?

Hopefully I did explain it not too complicated.

Thanks in advance
Flooo

Flooo (BOB member since 2013-01-23)

2

What do you mean by a “week”? Do you want the first 7 days of each quarter to count as week 1 regardless of which day of the week the quarter starts on?

Debbie

Debbie :uk: (BOB member since 2005-03-01)

3

To be honest, I would precalculated this in a time table.

But here is how you can do it in the report.

What you need:

  • an dimension object to retrieve the quarter (name= “Quarter”)
=Quarter([YourDate])
  • a measure which retrieves the 1st day of a quarter (name = “1st day”)
=Min([YourDate]) In ([Quarter])
  • a measure which will count the number of days between the 1st date of the quarter and “YourDate” (name = "NumDays)
=DaysBetween([1st day];[YourDate])+1
  • You can get the required week number by using this formula (or use it in an object):
=Ceil([NumDays]/7)

Obviously, you need all the dates in a quarter in your report for this to work.

I hope it helps.

Andy

andy.v :belgium: (BOB member since 2008-12-19)

4

Hi Debbie, Andy,

thanks a lot for your feebdack.

@ Debbie
Correct, I want the 1st 7 days each quarter counted as the 1st week

@ Andy,
Thanks for sharing all the formulas - it looks quite good however a correction is still needed which is related to Debbies question.

I do have an one extrat date of 3/30 and no previous ones; the system is calculating it at as 1st day and obviously as 1st week which is incorrect.

How do i need to amend the formula to calcuate the 1st day of a quarter without any link and references to my available dates?

Thanks again
Flooo

Flooo (BOB member since 2013-01-23)