Drill down Background Colour Expression Table

[shima_hish]

Hi,

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

First Level of Drill Down

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.

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.

Can please help? Thank you

[SergeyMak]

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.

[shima_hish]

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:

 

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

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:

Filtered by 'Delayed' in Subphase 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:

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]

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

[shima_hish]

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!