14 Replies Latest reply: Nov 10, 2017 6:00 AM by Mrutyunjaya Hiremath

How to put color (with expression) in drill down dimension?

Ivan Yang Jul 22, 2017 4:47 AM

Dear Qlik Sense Experts

I am new to Qlik Sense and I am a front end user

I have created a drill down dimension as below which I group ‘Product’ into 3 types: ‘A’, ‘B’, ‘C’

I already put color with expression for A1/A2/A3/A4/B1/B2/B3/B4/C. But how can I put color for A & B as well?

I want to put color for the upper layout (A/B/C) and lower layout (A1/A2/A3/A4/B1/B2/B3/B4/C) at the same time so that user can see color before or after they drill down on this pie

Is it possible to do so in Qlik Sense? If so, how to make it happen?

Thank you for your advice in advance

Test.qvf 192.0 K
Test.xlsx 10.4 K

Re: How to put color (with expression) in drill down dimension?

Andy Weir Jul 22, 2017 7:08 AM (in response to Ivan Yang)

If you are using the latest version of sense you can set colour of dimensions of you have it as a master item. perhaps look at that.
Like Show 0 Likes (0)

Actions
- Re: How to put color (with expression) in drill down dimension?
  
  Ivan Yang Jul 22, 2017 7:18 AM (in response to Andy Weir)
  
  Andy
  
  Thanks for the reply. I try but it doesn't work out
  
  Ivan
  
  Like Show 0 Likes (0)
  
  Actions
Re: How to put color (with expression) in drill down dimension?

Ruben Marin Jul 22, 2017 9:22 AM (in response to Ivan Yang)

Hi Ivan, that's happening because A and B have many different colors if it ended with none, you need to tell wich color use when 'A' or 'B'.

One option can be using "MinString(Product)" instead of "Product".
Or wich color do you want to assign to 'A' or 'B'?
Like Show 0 Likes (0)

Actions
- Re: How to put color (with expression) in drill down dimension?
  
  Ivan Yang Jul 22, 2017 9:40 AM (in response to Ruben Marin )
  
  Ruben
  
  Thank you so much for your reply
  
  Could you try to write down the expression base on this case so that I could do test in my apps?
  
  Should I simply add MinString(Product) into below expression?
  
  For question 2, I would like to put red color on 'A' and yellow on 'B'
  
  Ivan
  
  Like Show 0 Likes (0)
  
  Actions
- Re: How to put color (with expression) in drill down dimension?
  
  Ivan Yang Jul 23, 2017 2:14 AM (in response to Ruben Marin )
  
  Ruben
  
  I use 'MinString(Product)' instead of 'Product' and it works, but I still have questions
  
  1. I don't know why it works even after I check the function of 'MinString'. Can you help explain a little bit that why it works on this way?
  2. Color of A is same as A1, so does B
  3. What should I do if I want to put different color on 'A' & 'B'? I try to add expression as below but it doesn't work
  Thank you for your advise in advance
  
  Ivan
  
  Like Show 1 Like (1)
  
  Actions
  - Re: How to put color (with expression) in drill down dimension?
    
    Ruben Marin Jul 23, 2017 6:57 AM (in response to Ivan Yang)
    
    Hi Ivan,
    
    1. MinString gets the first value sorted alphabetically, so it gets A1 for A, and B1 for B.
    
    2. It was an example that it works when it has only one color to choose, but when it has more than one it will show grey.
    
    3. By your screenshots seems that product 'A' and 'B', doesn't exists, they are created using a calculated dimension.
    You can try with an expression like:
    // Your initial script without MinString
    If(Product='A1', RGB(),
    If(Product='A2', RGB(),
    ...
    If(Product='C', RGB(),
    // At the end add the colors for synthetic values, using Left() to get the first character
    If(Left(MinString(Product), 1)='A', Yellow(),
    If(Left(MinString(Product), 1)='B', RGB()
    ...
    
    Like Show 0 Likes (0)
    
    Actions
    - Re: How to put color (with expression) in drill down dimension?
      
      Ivan Yang Jul 23, 2017 10:05 AM (in response to Ruben Marin )
      
      Ruben
      
      Thank you so much for your detailed explanation
      
      I try to use Left in script but it still doesn't work
      
      Don't know which part is wrong in it.
      Ivan
      
      Like Show 0 Likes (0)
      
      Actions
      - Re: How to put color (with expression) in drill down dimension?
        
        Ruben Marin Jul 23, 2017 1:10 PM (in response to Ivan Yang)
        
        Hi Ivan, I'm not sure why is not working, I upload your sample modified.
        
        Test.qvf 192.0 K
        
        Like Show 0 Likes (0)
        
        Actions
      - Re: How to put color (with expression) in drill down dimension?
        
        Ruben Marin Jul 23, 2017 1:11 PM (in response to Ivan Yang)
        
        Ok, now I see, the 'real' products (A1, A2, ... C) should be without Minstring()
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to put color (with expression) in drill down dimension?
        
        Ivan Yang Jul 24, 2017 4:11 AM (in response to Ruben Marin )
        
        Dear Ruben
        
        Thank you so much for the feedback and I am so sorry to bother you again
        
        My client has made his request more specific.
        
        Now he groups 'NewRelease' / 'Aged NoPSD' / 'M1M2 Make' / 'M1M2 Fail' into 'No PSD'
        Groups 'OverAllFail M3' / 'OverAllFail M1M2' into 'OverAllFail'
        Keep 'Overall Make' as 'Overall Make'
        
        In this way, can we still use Minstring in this case? I try it but it doesn't work
        
        Do you have any idea base on this new request?
        
        You could refer to the attached raw data and qvf file for your reference
        
        Ivan
        
        Test.qvf 192.0 K
        
        Test.xlsx 10.5 K
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to put color (with expression) in drill down dimension?
        
        Ruben Marin Jul 24, 2017 5:27 PM (in response to Ivan Yang)
        
        Hi Ivan, still can be done as:
        if (Product='NewRelease',RGB (106,191,74),
        if (Product='Aged NoPSD',RGB (226,35,26),
        if (Product='M1M2 Make',RGB (255,106,0),
        if (Product='M1M2 Fail',RGB (62,141,221),
        if (Product='OverAllFail M3',RGB (233,107,175),
        if (Product='OverAllFail M1M2',RGB (111,113,112),
        if (Product='Overall Make',RGB (74,192,224),
        if (Match(MinString(Product), 'Aged NoPSD', 'NewRelease', 'M1M2 Make', 'M1M2 Fail'), blue(),
        if (Match(MinString(Product)='OverAllFail M1M2', 'OverAllFail M3'), yellow()
        )))))))))
        
        Another option can be to create a new field in script called [Product Group] (or similar), so the hierarchy has two levels: [Product Group] and [Product]. Then you have real values to select and apply colors using the real values for each level.
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to put color (with expression) in drill down dimension?
        
        Ivan Yang Jul 24, 2017 9:09 PM (in response to Ruben Marin )
        
        Ruben
        
        It works! I can't express how much I appreciate your patience and help!
        
        By the way, may I know how to create a new field in script called [Product Group] ?
        
        Do I have to do it at back-end side?
        
        Ivan
        
        Like Show 0 Likes (0)
        
        Actions
        
        Re: How to put color (with expression) in drill down dimension?
        
        Ruben Marin Jul 25, 2017 2:12 AM (in response to Ivan Yang)
        
        Hi Ivan, yes, it's done in the back-end; can be done using Script Editor or Data Manager.
        
        Using Data Manager, viewing as 'Tables', edit the table, you have a button called 'Add fields', with an option to add a 'calculated field'
        
        Like Show 0 Likes (0)
        
        Actions
Re: How to put color (with expression) in drill down dimension?

Mrutyunjaya Hiremath Nov 10, 2017 6:00 AM (in response to Ivan Yang)

Hi,
The simple way I found on this drilldown - coloring is,
writing IF() statement in a order of your last dimensions to be written first followed by subsequent dependent dimensions.Ex:
A -> B,C and C -> D,E,F then you should write an expression as;
If(Product='D',rgb(),
If(Product='E',rgb(),
If(Product='F',rgb(),
If(Product='B',rgb(),
If(Product='C',rgb(),
If(Product='A',rgb()))))))
Hope this helps.

Reg,
Mrutyunjaya
Like Show 1 Like (1)

Actions

Go to original post