6 Replies Latest reply: Jul 12, 2016 9:37 AM by florentina dogaru RSS

    Applying colour logic to a drill-down dimension

    Emma Carcas

      Hi,

       

      I have two dimensions RegionName and CountryName. I have created a drill-down dimension Region to Country so that in a bar chart by region I can drill down into the country. All of that is working fine.

       

      I have applied our branded colours which we have named RgbRegion and inserted that into the 'by expression' box for the bar chart colours. All of that works fine.

       

      My question is what logic do I need to ensure that the Country is coloured as per RgbCountry rather than RgbRegion.

       

      I was attempting =If(GetCurrentField(Region to Country)='Region,RgbRegion(),If(GetCurrentField(Region to Country)='Country', RgbCountry()))

       

      But the Drill-down dimension Region to Country doesn't come through as being able to pick from in the expression window.

       

      Your help is much appreciated.

       

      Thank you,

      Emma

        • Re: Applying colour logic to a drill-down dimension
          Rakesh Boorgu

          Is it a syntax issue?

          Please try this:

          =If(GetCurrentField('Region to Country')='Region', RgbRegion(),If(GetCurrentField('Region to Country')='Country', RgbCountry()))

          • Re: Applying colour logic to a drill-down dimension
            Anil Babu Samineni

            How about this?

             

            =If(GetCurrentField('Region to Country')='Region', Blue(),If(GetCurrentField('Region to Country')='Country', Red()),Green())

            • Re: Applying colour logic to a drill-down dimension
              AC BC

              Hi Carcas,

              Use [ ]  around the group name rather than ' '. using [ ] will give red lines , it is a bug from Qlik View end.

               

               

              Please attached with two dimensions.

               

              Product ID = Red

              Salesman ID = Green

              • Re: Applying colour logic to a drill-down dimension
                Rick Wild

                There's quite a lot going on here, so let's start with what works for me, then you can stop there if you like!

                 

                Here is the colour expression:

                if(getselectedcount(RegionName)=1

                ,rgb(CountryRed,CountryGreen,CountryBlue)

                ,rgb(RegionRed,RegionGreen,RegionBlue)

                )

                 

                Notes:

                1     Rename the colour columns to be distinct, otherwise you get a synthetic key and possibly confusion and a loop in the data model. I have left out the alpha, but the principle is the same.

                 

                2     Name the key field in the CountryColors table so that it associates with the country in the rest of the model, presumably CountryName.

                Like this:

                RegionColors:

                load * inline

                [

                  RegionName,RegionAlpha,RegionRed,RegionGreen,RegionBlue

                ...

                 

                CountryColors:

                load * inline

                [

                CountryName,CountryAlpha,CountryRed,CountryGreen,CountryBlue

                 

                 

                3     I have assumed your drill dimension is based on fields RegionName and CountryName. GetCurrentField come from QlikView and isn't part of Qlik Sense, but we can use GetSelectedCount instead. We get to CountryName when one and only one region is selected, ie getselectedcount(RegionName)=1. You need to reference the raw field name rather than the master item name. If your fields have non-alpha characters, eg space, put [] around them. This isn't required in the load inline statement.

                 

                4     I don't know what RgbRegion is, but the syntax in your example would be for a function. There is no RgbRegion function in Qlik Sense. It doen't work if RgbRegion is a master item. If it's a variable, you must define it without an '=' at the start, then reference it as $(RgbRegion) in the expression.

                 

                Regards,

                Rick