Dear Team,
I have a scenario to extract all the dates of Monday between intervals in the same column.
Example -
Data -
A B
New York 27-DEC-2016
New York 27-DEC-2016
New York 24-JAN-2017
Sydney 15-FEB-2017
Geneva 15-MAR-2017
Requirement Data
B A
26-DEC-2016 New York
26-DEC-2016 New York
02-JAN-2017
09-JAN-2017
16-JAN-2017
23-JAN-2017 New York
30-JAN-2017
06-FEB-2017
13-FEB-2017 Sydney
20-FEB-2017
27-MAR-2017
06-MAR-2017
13-MAR-2017 March
20-MAR-2017
27-Mar-2017
Continued upto Current date
22-Oct-2017
I have developed the report and used the following logics-
Variable A =TimeDim([B];WeekPeriod)
Variable B =RelativeDate([Variable A];-DayNumberOfWeek([Variable A]))
Variable C =RelativeDate([Variable B];+1)
but I have the following issues,
1.Date for December falls last in the column
2.Sum Value does not work
NOTE - Week Period not used because the same date returns with MULTIVALUE issue
=FormatDate(RelativeDate(TimeDim([B];WeekPeriod);+1);“dd-MMM-yyyy”)
Please check the attached screenshot for your kind reference.
Note -
Is it possible to use “CURRENT DATE” in the of the timedimension as Last date. Please advise.
Requesting to correct me on my mistakes and please suggest me for the best output.
Warm Regards,
Karthi
karthikgpks (BOB member since 2016-06-15)