7 Replies Latest reply: Jul 20, 2017 3:28 PM by Oleg Troyansky RSS

    Can We Convert Dimension in to Measure in Qlik Sense

    Hari Nag

      Hi Experts,

       

      Need to check with you guys, Can we add Dimension Field Name as a Measure for a particular Dimension. I have added field name as a measure if there is only one record per item. But when there are multiple field values there I am not able to see(Showing null Values)

       

      Data is Like this

      Country State

      USA      MA

      USA      OH

      USA      CA

      USA      MI

       

      I wanted to create a Table with just like above. But Dimension should be Country and Measure should be State.

       

      Thanks

      Hari

        • Re: Can We Convert Dimension in to Measure in Qlik Sense
          Oleg Troyansky

          Hi Hari,

           

          you misunderstand the meaning of Dimensions and Measures.

           

          Measures define "what the chart should calculate" - for example, Sales, Population, Quantity, ...

           

          Dimensions define "at what level to aggregate" - for example, Country, State, etc...

           

          There should only be one value of a Measure for each value of the dimension.

           

          Since there are multiple States per Country, State cannot be a Measure for the dimension Country. You could, if you really wanted to, to reverse the two and show State as a Dimension and only(Country) as the Measure. There should be always one Country per State, so it should work.

           

          In your case, you can create a Table with two Dimensions and no Measures, and you will get the desired table.

           

          Cheers,

          Oleg Troyansky

          Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

            • Re: Can We Convert Dimension in to Measure in Qlik Sense
              Mary Jo Taft

              what if you wanted to count the number of states in a country (or it could  be states, provinces, etc)?  Wouldn't country be considered a Dimension and State a measure in that case?  I know this isn't what he was asking based upon the table he is showing, but there could be cases where a field could be either a dimension or measure depending upon the request.

                • Re: Can We Convert Dimension in to Measure in Qlik Sense
                  Oleg Troyansky

                  Hi Mary,

                   

                  in your example, Country could be the Dimension, and count(distinct State) could be the Measure. So, to your point, any field can participate in a Dimension or participate in a Measure, but in most cases, a Field itself ("naked") cannot be a measure. If there is always one value for this Field per each Dimension's value, then it will technically work, and the "naked" field name will get automatically enclosed in the function "only":

                   

                  only(Field)

                   

                  cheers,

                  Oleg Troyansky

                  Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!

                    • Re: Can We Convert Dimension in to Measure in Qlik Sense
                      Mary Jo Taft

                      Oleg -

                      Absolutely true.  I was just wanting to clarify for myself because I can think of other applications.  For instance, let's say I want to know which state in the US has the most counties.  County can certainly be a dimension but in this case it would be a measure with State being the dimension.

                      OTOH - if I wanted to see what county in Michigan had the largest population, then county would be a dimension and population would be my measure.

                       

                      Is my understanding correct here?  Again ... just verifying since I am still very new.  I might check out your book!

                       

                      Mary Jo

                        • Re: Can We Convert Dimension in to Measure in Qlik Sense
                          Mark Ritter

                          In your example State would be the Dimension and Count(County) would be the measure. 

                           

                          So it is not that the field is either a Dimension or a Measure.  If depends on how you are using the data.  A calculation like Count of Counties would be a measure even though in a different situation County could be the dimension and the measure might be the Population in each county.

                            • Re: Can We Convert Dimension in to Measure in Qlik Sense
                              Mary Jo Taft

                              yes - that is what I said (or meant if it wasn't clear).  A field could be used as a dimension or a measure depending on the question being asked of the data.  I had 2 examples

                               

                              1) which state in the US has the most counties

                                   then dimension = state

                                   measure = count(counties)

                               

                              2) what county in Michigan had the largest population

                                   dimension = county

                                   measure  = population in each county

                                • Re: Can We Convert Dimension in to Measure in Qlik Sense
                                  Oleg Troyansky

                                  Hi Mary,

                                   

                                  yes, your examples are correct. If you want to see all states and their corresponding numbers of counties, then you can sort by expression and determine what state has the highest number of counties.

                                   

                                  If you wanted to get a single result - the State that has the highest number of counties, without the need in making that conclusion visually, you could conceptually calculate that, using advanced aggregation AGGR.

                                   

                                  You can learn these advanced techniques from my lecture on Set Analysis and Advanced Aggregation at the Masters Summit for Qlik - coming to Boston, MA this October!

                                   

                                  cheers,

                                  Oleg Troyansky