Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shima_hish
Partner - Contributor II
Partner - Contributor II

Drill down Background Colour Expression Table

Hi,

I would like to apply different colour for different level of drill down.

First Level of Drill Down

Subphase.JPG

Second Level of Drill Down (Delayed is filtered from the first level drill down)

I want this level to show different colour of background for On Track, Completed and Delayed.

Key Activities.JPG

Third Level of Drill Down (Delayed filtered from the second level of drill down)

I want this level also to show different colour. for this case, In Progress should have amber background colour.

ACtvities.JPG

Can please help? Thank you

Labels (2)
4 Replies
SergeyMak
Partner Ambassador
Partner Ambassador

Hi,

I think it can be solved by using GetObjectDimension() function.

In your case

Pick(Match( GetObjectDimension(),'Sub-phase','Key Activities','Activities'),

       Pick(Match([Subphase Health Status],'Completed','Delayed','On Track'),

       green(),lightred(),lightgreen())),

        lightred(),red()

)

I might miss some brackets, but that should give an idea.

Regards,
Sergey
shima_hish
Partner - Contributor II
Partner - Contributor II
Author

Hi Sergey,

 

Thanks a lot for your reply.

As suggested by you, I pasted the following code in background colour expression:

Pick(Match( GetObjectDimension(),'Sub-phase','Key Activities','Activities'),

Pick(Match([Subphase Health Status],'Completed','Delayed','On Track'),

green(),lightred(),lightgreen()))

It gives the following result:

Subphase.JPG 

However, if I drilled down on 'Delayed', it will show the following:

drill2.JPG

So I made some adjustment on the code to the following:

Pick(Match( GetObjectDimension(),'Sub-phase','Key Activities','Activities'),

Pick(Match([Subphase Health Status],'Completed','Delayed','On Track','At Risk'),green(),lightred(),rgb(50,205,50),RGB(255,191,0)),
Pick(Match([Health Status],'Completed','Delayed','On Track','At Risk'),green(),lightred(),rgb(50,205,50),RGB(255,191,0)),
Pick(Match([Workplan Status],'Completed','In Progress','Not Started'),green(),RGB(255,191,0),lightred()))

It's working fine on the following when I tried to drilldown further on 'Delayed' status:

drilla1.JPGFiltered by 'Delayed' in Subphase Health StatusFiltered by 'Delayed' in Subphase Health StatusFiltered by 'Delayed' in  Health StatusFiltered by 'Delayed' in Health Status

However, when I tried to filter by On Track on the first level, then drilled down further in 1st column from Testing > SIT SRM & MM, this is what happened:

drillb1.JPGdrillb2.JPGdrillb3.JPG

from my observation, the desired colour for the status will display if the 1st column is drilled down to Activities.

Just want to give you a heads up, the workplan status and health status is actually for Activities. the Subphase health status is for Sub-phase.

is there a way that can make the color of second column independent on what 1st column are showing? or you have any other suggestion?

Really appreciate your reply! 🙂 Thank you

 

SergeyMak
Partner Ambassador
Partner Ambassador

Thanks for heads up.

'is there a way that can make the color of second column independent on what 1st column are showing? or you have any other suggestion?'

Sure there is. You can still use IF statement where appropriate or make a default colour using Wildmatch, instead of Match.

In that case, you can have '*' as last option and assign an appropriate colour in Pick as the last option.

 

P.S.: please don't forget to mark an appropriate response as correct or helpful

Regards,
Sergey
shima_hish
Partner - Contributor II
Partner - Contributor II
Author

Hi,

Can you please give me the example of it? I'm still new with Qlik Sense. I am quite familiar with IF statement but not with WildMatch and Pick functionality. What do you mean yea when you mentioned about  '*' as last option in Pick? and how to use If, Pick and wildmatch together?

 

is it like this?

if(
Pick(wildMatch([Subphase Health Status],'Completed','Delayed','On Track','At Risk'),green(),lightred(),rgb(50,205,50),RGB(255,191,0)),
Pick(wildMatch([Health Status],'Completed','Delayed','On Track','At Risk'),green(),lightred(),rgb(50,205,50),RGB(255,191,0)),
Pick(wildMatch([Workplan Status],'Completed','In Progress','Not Started'),green(),RGB(255,191,0),lightred()),


)

 

Thank you!