Highlight Current, Past and Future Data in Crosstab

Those who have tried conditional formatting in crosstab know that its a pain to do so especially if your condition is based on data items not part of the crosstab.

Requirement: Display for each month, for each weekending date the Revenue spread across the days of the week. Highlight the past weeks and past dates in Current week in Orange and current date and future dates in current week in Yellow and all other Future Dates in white.



Now the challenge is that the dates are not displayed rather the Day of Week. So the only items we have to work with are Weekending Date and the Day of Week.

Solution:

Step 1: Create a list report to display the Months.

Step 2: Create the crosstab inside the list with master - detail set on Month and section on Month.

Step 3: Drag the Weekending Date data item and create the Weekday data item: _day_of_week([DTE],1) and use case statement to display Monday - Friday.

Step 4: Create the conditional variable to highlight the required data as below:


case when
(
string2date([SummaryQ2].[Week Ending Display]) < _add_days(date2timestamp(today()),7 -_day_of_week(date2timestamp(today()),1))
)
or
(
string2date([SummaryQ2].[Week Ending Display]) = _add_days(date2timestamp(today()),7 -_day_of_week(date2timestamp(today()),1))
and _day_of_week(date2timestamp(today()),1) > ([SummaryQ2].[Days])
)
then 'Orange'
when string2date ([SummaryQ2].[Week Ending Display]) = _add_days(date2timestamp(today()),7 -_day_of_week(date2timestamp(today()),1))
and _day_of_week(date2timestamp(today()),1) < = ([SummaryQ2].[Days])
then 'Yellow'
else 'White' end


Step 5: Associate the crosstab intersection with the conditional variable and set the corresponding colors.