Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Specialist II
Specialist II

Is this what you are looking for?

Untitled.jpg

View solution in original post

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

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

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

Hi,

May be like as attached file?

Regards,

Andrey

Not applicable
Author

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

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
Specialist II
Specialist II

Is this what you are looking for?

Untitled.jpg

View solution in original post

Not applicable
Author

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
Author

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
Specialist II
Specialist II

Hi

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