on 12-12-2016 8:50 PM
Hello everyone, there is something I’m trying to do involving dates that I have not been able to successfully create.
I am making a report that will be run once weekly. In the report there will be six columns where I’m pulling in weekly amounts of a metric for each of the following six weeks. I want to make headers for the columns that read “Week of xx/xx/xxxx”. The trick is that I need those headers to reflect the dates of the next six Mondays, regardless of when the report is run.
For now, as a work-around, I’m planning to just manually run the report every Friday, and have created six variables to read into the column headers that just count 3, 10, 17, 24, 31, and 38 days from the day the report is run. For example, for the first column –
=”Week Of ” +RelativeDate(CurrentDate();3)
This is working just fine, given of course that I only ever run the report on a Friday. Can anyone give me any pointers on how I can set it up to always read in the dates for all six following Mondays, regardless of the day on which the report is run?
Thank you!
– Zac
ReplyHi Zac,
Create the below variables:
v_CurrDt = CurrentDate()
v_DayNmofCurrDt = Dayname([v_CurrDt])
v_1stMondayaftertoday = If([v_DayNmofCurrDt]="Tuesday") Then RelativeDate([v_CurrDt];6)
ElseIf([v_DayNmofCurrDt]="Wednesday") Then RelativeDate([v_CurrDt];5)
ElseIf([v_DayNmofCurrDt]="Thursday") Then RelativeDate([v_CurrDt];4)
ElseIf([v_DayNmofCurrDt]="Friday") Then RelativeDate([v_CurrDt];3)
ElseIf([v_DayNmofCurrDt]="Saturday") Then RelativeDate([v_CurrDt];2)
ElseIf([v_DayNmofCurrDt]="Sunday") Then RelativeDate([v_CurrDt];1)
v_2ndMondayaftertoday = RelativeDate([v_1stMondayaftertoday];1;WeekPeriod)
OR
= RelativeDate([v_1stMondayaftertoday];7)
(You can use the parameter WeekPeriod, if you have BO version 4.1 SP6 or later.)
Similarly, you can create variables for the following 3rd, 4th, 5th, 6th Mondays.
Hope that helps.
Thanks,
Mahboob Mohammed
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This is exactly what I needed! And I understand the logic so I can do permutations of this at a later point. Thank you Mahboob!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.