How to sort by background colour?

[#1]

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.

[Anil_Babu_Samineni]

Please attach your QVW file. And then we can explain for you where you make the mistake. And in sort tab only you need to do? But use Numeric Value as Ascending??

[jonathandienst]

Can you use a straight table rather than a pivot table?

A straight table can sort on any column, including expressions.

You can't actually sort by colour, but you could sort by the same expression which is used to determine the colour (and you could hide the expression column if you don't want to show it).

[ahaahaaha]

Hi,

May be like as attached file?

Regards,

Andrey

[#5]

Hi guys,

Thanks for the prompt response.

I can't unfortunately upload the original file as it is has data from my work.  But I created an example file and have attached it here.

I changed the table from a Pivot Table to a Straight Table ( like Jonathan suggested ) but I still can't get it to work.

Please take a look and tell me what I would need to do?

[dapostolopoylos]

Maybe you should try to change chart type (straight table is a good choice) and play with the Background Color attribute of your Expression

[lorenzoconforti · Accepted solution]

Is this what you are looking for?

[#8]

That is exactly what i'm looking for.  I tried your solution ( I promoted the date field to the top, clicked on numeric sort, and unclicked any sorts for all the other dimensions ) and it is more sorted than it was before, in that there is a better increase in grouping, but the dates aren't completely sequential.  Here is a screen shot:

As you can see, the is still a disjoint.  I can't imagine why that would be.

[#9]

That's mate,  I found what the problem was.  It was a formatting issue with the date.  I fixed it then applied your solution and it works now.  Thank you very much.

[lorenzoconforti]

Hi

Looks like it's not sorting the date correctly. Can you please post the application ?