5 Replies Latest reply: Oct 23, 2018 4:37 AM by Helge Jørgensen RSS

    Create a dimension

    Helge Jørgensen

      Qlik experts,

       

      Can I create a dimension based on a measure?

      I need to create a chart where the dimension (x-axis) is the name of a measure. The measure is a count for grater than 8,7,6,5,4,3,2,1. The x-axis needs to be 8,7,6,5,4,3,2,1 and the y-axis is the count of custID greater than 8,7,6….

      count({<Value1={">8"}>} CustID)

       

      Please see sample app:

        • Re: Create a dimension
          Shahbaz Khan Mohammed

          In Dimension you can use

          =if(Value1>=8,Value1)

           

          or You can do this in script as well

          Data:

          LOAD

              Dato,

              CustID,

              Value1,

             if(Value1>=8,Value1) as [Value8],

              Value2

          FROM [lib://Data/SampleData.xlsx]

          (ooxml, embedded labels, table is Sheet1);

          And then in front end just use [Value8] as your dimension

          For measure use

          count({<Value1={">8"}>} CustID)

            • Re: Create a dimension
              Helge Jørgensen

              Thanks!!

               

              Can I add more values to the dimension? Like -  if(Value1>=7,Value1),[Value7],  if(Value1>=6,Value1) as [Value6]?

               

              And can these be grouped on top of each other? See picture below (this is created based on a calculated excel-file)

               

              samplepic.PNG

                • Re: Create a dimension
                  Shahbaz Khan Mohammed

                  Main X-axis would be only 1 but you can add 1 more dimension thats it.

                  For measure you can use Multiple Alternative measures but only 1 measure can be seen at a time


                  As for creating buckets you can create buckets based on number....


                  Search for creating Age buckets in community. You'll get an idea how to create new values based of >=8 , <=5 etc


                  • Re: Create a dimension
                    Stefan Wühl

                    Maybe add something like this to your script:

                     

                    For i = 1 to 8

                     

                    Groups:

                    LOAD DISTINCT

                         Value1,

                         $(i) as Group

                    Resident Data

                    WHERE Value1 > $(i);

                     

                    Next i

                     

                    Then use Group as dimension and just a Count(CustID) as expression.

                     

                    edit: added DISTINCT

                      • Re: Create a dimension
                        Helge Jørgensen

                        Thanks, Stefan

                         

                        Your suggestion works fine!

                         

                        One follow-up question: In Data load editor I normally use Qualify *;  and Unqualify Dato; to connect keys between datasets. When I use this, loading following your suggestion I get an error message saying that Vaule1 is missing. When I remove Qualify and Unqualify, it works fine.

                         

                        Do you have any suggestion why this happens? Or should I not use qualify and Unqualify at all in Data Load Editor?

                        Thanks for your help!