2 Replies Latest reply: Jan 3, 2018 6:19 AM by Anibal Martinez-Sistac RSS

    Colored conditional line-charts in dimensions with high number of categories

    Anibal Martinez-Sistac

      It is well explained by Steve Street on color by expression - QlikSense, and Kaan Erisen in Change color in charts with conditional dimensions and aggregations how to create conditional colored line charts. Nevertheless, this option is perfect to be applied on Line Charts cases were you have few-categories dimensions (e.g. Sales Sum by Continent), as this solution uses the function ValueList and each category of a dimension should be entered manually. In the case of many-categories dimensions (e.g. Sales Sum by City_ID or Customer_ID) it does not work well, as it is not practical to enter manually the ID´s of all the cities.

       

      My question is: is it possible to create this condition-colored Line Charts over many-categories dimensions without entering them manually with the ValueList approach?

       

      I explain a reproducible file example attached:

       

      Variable extensions values in the app attached:

      BeginPromoA = 01.05.17

      EndPromoA = 03.05.17

      BeginPromoB = 04.05.17

      EndPromoB = 06.05.17

      BeginPromoC = 07.05.17

      EndPromoC =  08.05.17

       

      First I create a conditional colored line chart for an example where I only have 4 categories. It works well:

       

      Data

           Dimensions:

                Date

                Synthetic Dimension -> Fx expression:

      ValueList('First Class', 'Second Class', 'Standard Class', 'Same Day')

       

           Measures:

                Sales -> Fx expression:

      if(  ValueList('First Class', 'Second Class', 'Standard Class', 'Same Day')='First Class', SUM({<[Ship Mode]={'First Class'}>}Sales),

      if(  ValueList('First Class', 'Second Class', 'Standard Class', 'Same Day')='Second Class', SUM({<[Ship Mode]={'Second Class'}>}Sales),

      if(  ValueList('First Class', 'Second Class', 'Standard Class', 'Same Day')='Standard Class', SUM({<[Ship Mode]={'Standard Class'}>}Sales),

      if(  ValueList('First Class', 'Second Class', 'Standard Class', 'Same Day')='Same Day', SUM({<[Ship Mode]={'Same Day'}>}Sales)

      ))))

       

      Appearance

           Colors and Legend -> Fx expression:

      if((Date>=date(date#('$(vBDA_3)','DD.MM.YY'),'DD.MM.YY')) and (Date<=date(date#('$(vBDA_4)','DD.MM.YY'),'DD.MM.YY')),LightRed(),

      IF(MATCH(ValueList('First Class', 'Second Class', 'Standard Class', 'Same Day'),'First Class','Same Day','Second Class'),Blue(),LightGreen()))


      The resulting graph is perfect:

      imagex1.png

      Now, what I want to create is an analogous example with more categories, with error results. I.e.

       

      Data

           Dimensions:

                Date

                City

       

           Measures:

                UABs (Unique Active Buyers )-> Fx expression:

      num(Count(Distinct [Customer ID]),'#,##0',',' )


           Cities to be coloured in green:

                - New York City

                - Los Angeles

      image2x.png

       

      Thanks in advance for all your answers. Anibal

        • Re: Colored conditional line-charts in dimensions with high number of categories
          kaan erisen

          Hi Anibal,


          "My question is: is it possible to create this condition-colored Line Charts over many-categories dimensions without entering them manually with the ValueList approach?"


          Actually you don't need to use 'valuelist' method to get this chart you desired with this data.


          Dimensions:

                    Date

                    [Ship Mode]

          Measures:

                  Sum(Sales)

          Appearance

              Colors and Legend -> Fx expression:

                    IF((Date>=date(date#('$(vBDA_3)','DD.MM.YY'),'DD.MM.YY')) and (Date<=date(date#('$(vBDA_4)','DD.MM.YY'),'DD.MM.YY')),LightRed(),

          IF(MATCH([Ship Mode],'First Class','Same Day','Second Class'),Blue(),LightGreen()))


          For second graph,

          Data

               Dimensions:

                    Date

                    City

           

               Measures:

                    UABs (Unique Active Buyers )-> Fx expression:

          num(Count(Distinct [Customer ID]),'#,##0',',' )


               Appearance

                   Colors and Legend -> Fx expression:

                    if((Date>=date(date#('$(vBDA_3)','DD.MM.YY'),'DD.MM.YY')) and (Date<=date(date#('$(vBDA_4)','DD.MM.YY'),'DD.MM.YY')),LightRed(),

          IF(MATCH(City,'New York City','Los','Los Angeles'),LightGreen(),Blue()))

           

               Cities to be coloured in green:

                    - New York City

                    - Los Angeles

           

          İf you have to many categories maybe you can define it in script level.

           

          Hope it helps,