Color Cells in Cross Table According to Historical Data Not Displayed in Cross Table


Short Story: I have a cross-table that displays the most recent VALUE for a given COUNTRY (columns of the cross table) and a given METRIC (rows of the cross table). I want to color each cell of the cross table not with reference to the other cells, but with reference to earlier values for that COUNTRY and METRIC. In other words, each cell displays the latest point of a distinct time series, and I want to color each cell with the z-score of that point (the most recent value minus the average value over all history, normalized by the standard deviation over all history). Can anyone think of a way to do this?  Thanks very much!

Detailed Version, in case that's not clear: The columns in my data table are COUNTRY, METRIC, DATE,  and VALUE.  For each COUNTRY-METRIC pair, therefore, I have a time series of VALUE across DATEs.

I've built a Cross Table where the rows are all possible values of SECTOR, and the columns are all possible values of COUNTRY. Within the Cross Table, I display the latest (as in maximum DATE) VALUE for that COUNTRY-METRICpair. I'm currently doing this with a simple Last() aggregation. So in its current form, the Cross Table does not show anything happening over the DATE dimension - just the latest VALUE in each category.

But I want to color the individual cells of the cross table according to the latest VALUE's historical z-score with respect to the same COUNTRY-METRICpair over previous DATES. So the color should be determined NOT by each cell's relationship to other cells in the table, but rather by each cell's relationship with its own history (previous DATEs) that are not shown in the table.

So for each value in the table, I'd like somewhere (calculated column? or a custom expression for the coloring that calculates on the fly) to calculate the z-score (latest value less average value across all DATES, normalized by std dev across all DATES) for a given COUNTRY-METRICpair and color according to that. But I'm struggling to define the Color axis with reference to anything not displayed in the table.

Any thoughts on how I might pull this off? Thank you!






(1) Answer