Hi guys,
The situation is that I have a pivot table with three dimensions Report ID, Report Name, Dept. And under the 'Expressions' tab I added a '[Completion Date]' dimension so that the date that a report requires reviewing is shown.
Whilst still in the 'Expressions' tab, I changed the background colour of the [Completion Date] column cells so that if a completion date of a report is more than 100 days away from the user's current date, then make the background colour green. It the date is between 30 and 100 days, make the background yellow. If the date is less than 30 days away, then make it red. If the cell has a null value then leave it uncoloured.
As an example my table looks something like this (I can't change the cell colours here, sorry so I wrote the colour in the cells😞
| Report ID | Report Name | Dept | [Completion Date] |
|---|
| 1 | A | Marketing | 9/11/18 ( Yellow ) |
| 2 | B | Technology | 1/3/18 ( Green ) |
| 3 | C | Sales | 27/8/17 ( Red ) |
| 4 | D | HR | 1/1/18 ( Green ) |
| 5 | E | Research | 17/4/17 ( Red ) |
| 6 | F | Auditing | - |
| 7 | G | Purchasing | - |
So I would like to sort by the background colour of the [Completion Date] cells or by the date itself, so that those cells that are red come at the top and in date order ( so that they can be dealt with first ), then the yellows, then the greens. So the table should look something like:
| Report ID | Report Name | Dept | [Completion Date] |
|---|
| 5 | E | Research | 17/4/17 ( Red ) |
| 3 | C | Sales | 27/8/17 ( Red ) |
| 1 | A | Marketing | 9/11/18 ( Yellow ) |
| 4 | D | HR | 1/1/18 ( Green ) |
| 2 | B | Technology | 1/3/18 ( Green ) |
| 6 | F | Auditing | - |
| 7 | G | Purchasing | - |
I tried going into the 'Sort' tab but [Completion Date ] isn't there as one of the dimensions. Does anyone know how I can achieve the order that I want?
For those that are kind enough to respond, please don't attach .qvw files as I have the personal edition and can't open them up anymore.
Thanks in advance.
