Invariably Ignored

Harold Ramis died this week. Most of you are saying to yourself, "Aw, that's sad... Now remind me who he is?"

Without Harold, modern society wouldn't be possible.

He wrote, directed, produced or starred in the most important films of all time. Caddyshack. Vacation. Year One. Animal House. Ghostbusters. Heavy Metal. Stripes.

Not to mention the most hated, most reviled, worst movie ever, Groundhog Day. I'm not kidding. Don't watch it. If you have watched it, forget it. Delete it from Wikipedia's Harold Ramis page. If you see it in the store, yell at the store manager and throw every copy in the trash. Then find Bill Murray and Zombieland his ass. Then do it all again. And again. And again.

Ignore it. Just like you need to ignore fields with your set analysis.

Many of the objects that we build are designed to show specific buckets of time. For example, you build a chart with a rolling three months and a sparkline with a rolling six months:

The expression for a column would look something like:

Count({<Calendar.RelativeFiscalMonth = {'Current Month - 2'}>} Closed)

But since you have all of the date filters, you would have to ignore anything that might wreck your charts. So you add a bunch of 'Calendar.XXX = ' into your set analysis and pretty soon it get's out of control. In a typical document, you might have 20 different date fields in your master calendar:

Typing that into each expression's set analysis is crazy. The solution is to create a variable and use $-sign expansion.

Step One - A Little Excel Magic

OK, just basic Excel.

Export the list of fields that you want to ignore. Add commas and '=' signs, then concatenate:

Don't forget to add square brackets if your field names have spaces.

Step Two - Create a Variable

Call it 'vIgnoreCalendar'. Copy your new Excel column into the Definition:

Step Three - Use It

Instead of the example above (which might go to zero if you choose the wrong date filter), use the new variable:

Count({<$(vIgnoreCalendar), Calendar.RelativeFiscalMonth = {'Current Month - 2'}>} Closed)

Which is so much nicer than:

Count({<
 Calendar.Date =
,Calendar.DayOfWeekName =
,Calendar.DayOfWeekNum =
,Calendar.DayOfYear =
,Calendar.Month =
,Calendar.MonthName =
,Calendar.MonthNameAbbr =
,Calendar.Quarter =
,Calendar.QuarterName =
,Calendar.Week =
,Calendar.WeekDay =
,Calendar.WeekEnding =
,Calendar.WeekStart =
,Calendar.Year =
,Calendar.YearMonth =
,Calendar.YearMonthAbbr =
,Calendar.YearQuarter =
,Calendar.YearWeek =
 Calendar.RelativeFiscalMonth = {'Current Month - 2'}>} Closed)

Where you put the variable in your set analysis is very important. Since the first line of the variable definition doesn't start with a comma, put it first. Otherwise you'll get an error because your expression won't have commas in the right places.

Finally

Teaching others is the most rewarding activity that a person can participate in. Just ask Russell Ziskey about teaching Basic English in the opening scenes of Stripes:

Son of Bitch. Shit.

Eloquence in film. It takes my breath away.