I want to have my timeline report to always have the date span "Starting Date: and Ending Date: to be dynamic. A week prior to the current day and two months out from the current day. Do I have any options for editing this starting and ending date? This is for a time off chart where past time off does not need to be seen.
I did something like this by making two new fields:
[Start Date for Timeline]
[End Date for Timeline]
My start date I wanted to only show it was 4 weeks ago or 90 days ahead, unless the start date was really old, but the project was ongoing, then i just set the start date for exactly 4 weeks ago (just to be sure the start date existed in the realm)
[Start Date for Timeline] =
var date Start = [Start Date]
If(
$Start>Today()-Days(28) and $Start < Today() + Days(90), $Start,
$Start<=Today()-Days(28) and not IsNull([End Date for Timeline]), Today()-Days(28),
null)
End date was only shown for that 4 week to 90 day period. I have 97 in my formula because in the event the start date was 90 days out, then the end date would be 97 days out so at least i would show a week of that project (or else my start and end date in the exact scenario would look like a single day).
var date End = [End Date]
If($End>=Today()-Days(28) and $End < Today() + Days(97), $End)
I think if you change the 28 days to 7 days, and the 90 days to 60 days, that should work for your purposes.
I can see this possibly working if it is a global variable (I don't know that QB has that option). However, how I see what you're saying as a field in the same table as my start and end date for time off. Once the script has a date, it will fill in the field. Not to be re-created untell another trigger happens. I need a trigger to activate when the TimeLine table displays. Or are you placing the script in the TimeLine settings, if so I do not see a way to do this. I am playing with the idea of adding a hidden completed check box field to be selected once the date has passed, then exclude (or include) from a search.
I guess it is true that my formulas really only make it so the min date is a week prior and the max date is 60 days out. however, if no projects exists on those extremes, the timeline will be smaller. Only way I can think of always having a set timeline at both extremes is to make a psuedo event, and in the start date formulas i posted add a first line that basically sets the pseudo event to the extremes. This would mean your timeline would always have one psuedo event with the entire duration of the timeline. I think i remember running into that limitation even with my work around, but we had enough projects happening where my extremes are almost always satisfied.
Thank you so much for your insight. I will see what I can do. I I find something I will post.
Ok, worked it out a couple of ways but the easy way is to do this. Go into your timeline report, go to Filters, Initial, select filter day off records, all, Select start of the project and select is on or after select days, my case 7 and select days in the past. You could then add another line of end days is on or before days in the future for me this would be 30. Dumb I know I should have seen this sooner but I figured it out. I hope this will help you if your looking.
