Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

How to sort by background colour?

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 IDReport NameDept[Completion Date]
1AMarketing9/11/18 ( Yellow )
2BTechnology1/3/18 ( Green )
3CSales27/8/17 ( Red )
4DHR1/1/18 ( Green )
5EResearch17/4/17 ( Red )
6FAuditing-
7GPurchasing-

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 IDReport NameDept[Completion Date]
5EResearch17/4/17 ( Red )
3CSales27/8/17 ( Red )
1AMarketing9/11/18 ( Yellow )
4DHR1/1/18 ( Green )
2BTechnology1/3/18 ( Green )
6FAuditing-
7GPurchasing-

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.

1 Solution

Accepted Solutions
lorenzoconforti
Contributor III

Re: How to sort by background colour?

Is this what you are looking for?

Untitled.jpg

View solution in original post

10 Replies

Re: How to sort by background colour?

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??

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
MVP
MVP

Re: How to sort by background colour?

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).

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
ahaahaaha
Honored Contributor

Re: How to sort by background colour?

Hi,

May be like as attached file?

Regards,

Andrey

Not applicable

Re: How to sort by background colour?

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
Contributor III

Re: How to sort by background colour?

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

Capture.JPG

Father/Husband/BI Developer
lorenzoconforti
Contributor III

Re: How to sort by background colour?

Is this what you are looking for?

Untitled.jpg

View solution in original post

Not applicable

Re: How to sort by background colour?

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:

screenshot.jpg

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

Not applicable

Re: How to sort by background colour?

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
Contributor III

Re: How to sort by background colour?

Hi

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