topic Re: Conditional Formatting by position in QlikView

Conditional Formatting by position

alisonpwallis — Wed, 25 Nov 2020 16:16:04 GMT

Dear All

I have a table showing two levels of information - a course and the components in that course (e.g. exam, coursework). I want to shade the background of the cells depending on which course it is like this:

Header 1	Header 2	Header 3
Course 1	Component 1	Score
Course 1	Component 2	Score
Course 2	Component 1	Score
Course 2	Component 2	Score
Course 2	Component 3	Score

So for example - the two rows with Course 1 in would be blue and the three rows where Course 2 is would be green (and then back to blue if there was a course 3).

I don't know how many components each course might have. The only thing I can do is note that the course has changed. I've tried using 'Above' and 'Below' but I can't get it to work. So I create a new expression which is just the course ID (called [Course]) and then I do:

if(Above([Course])=Below([Course]), RGB(235,100,30), RGB(152,87,174)

This makes everything one colour!

Does anyone have any suggestions for this?

Thanks

Alison

Re: Conditional Formatting by position

Anil_Babu_Samineni — Mon, 28 Nov 2016 13:10:02 GMT

You may try like below for sample

=If([Header 1] = 'Course 1', Red(), If([Header 1] = 'Course 2', Green(), Blue()))

Re: Conditional Formatting by position

alisonpwallis — Mon, 28 Nov 2016 13:11:59 GMT

Hi Anil

Thanks for your reply. The problem is I don't know which courses they will actually be since they will be depending on which tutor has logged in to the system so I can't colour code them all. There are around 800 in total!

I just want them to be shaded differently so that when you see a long list you can see where each new course starts.

Thanks

Alison

Re: Conditional Formatting by position

trdandamudi — Mon, 28 Nov 2016 13:14:51 GMT

See if the below helps:

Go to the dimension and expand the plus sign next to it.

Select Background color and click on Edit.

Now use the below expression:

=If([Header 1]='Course 1',Blue(),If([Header 1]='Course 2',Green(),Blue()))

Apply the same expression for all the dimensions and you will get the result. Hope this helps.,...

Re: Conditional Formatting by position

MK_QSL — Mon, 28 Nov 2016 13:15:10 GMT

Create an ID field as below..

Load *, AutoNumber([Header 1]) as ID Inline

[

Header 1, Header 2, Header 3

Course 1, Component 1, 100

Course 1, Component 2, 200

Course 2, Component 1, 100

Course 2, Component 2, 200

Course 2, Component 3, 300

Course 3, Component 1, 100

Course 4, Component 2, 200

Course 4, Component 3, 300

];

Now use below expression for background color.

=IF(Even(ID),Red(),Green())

Check enclosed file for sample.

Re: Conditional Formatting by position

alisonpwallis — Mon, 28 Nov 2016 14:07:51 GMT

Thank you very much Manish - that worked perfectly!

Thanks

Alison