Hi Community, I'm generally familiar with master calendars and have plenty of ideas to experiment with, but posting here in case anyone has a ready solution and I spend my day hitting dead-ends.
So we have a regular master calendar setup to follow calendar years, but now I need customized calendars that track with 4- and 6- week activities that various clients have. The database table I have for these activities is like this:
ActivityID StartDate EndDate
123 1/1/2016 1/28/2016
456 2/1/2016 2/28/2016
Specifically, I need activity calendar weeks that flow from the various StartDates, so when the end user selects challenge 123, the Challenge Weeks go 1 to 4 in 7-day increments from 1/1/2016. And if they select challenge 456, the challenge weeks start week 1 on 2/1/2016.
Things I ruled out:
Expression in the table didn't like me inserting min(MCDate) stuff into the if statements.
Calculated dimension laughed at my dual if statement and gave a //error in calculation
I have a master calendar with all the days populated, and did like:
sum(if(MCDate>=min(MCDate) AND MCDate<=(min(MCDate)+6), Minutes)) for the expression that failed.
I pushed the date range definitions out to a dual if statement in the calculated dimension, and cut the expression down to just sum(Minutes), but the calculated dimension didn't work either.
So I'm looking back at the script to see if I can create a ChallengeWeeks field there to use as a dimension on the front, and sum(Minutes) on that field in the front. I'll be playing with that now, and look forward to any input from anyone else who has had the same kind of requirements. Thanks!
***Edit: It was min(MCDate) I was trying to work with initially and user would select the challenge dates on the master calendar. I'm just now bringing in the actual challenge dates in script to see if I can make smarter expressions. I might actually be okay doing the expression with StartDate and StartDate+6 once I have it in actually, since no min() to confuse it.
Edit2: And making the challengeweeks field in script is probably the optimal solution so the tables on the front don't have to handle those computations every time a selection is made. I will do that if I see how.