Can We Convert Dimension in to Measure in Qlik Sense

[h_prakash]

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

[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

[mjtaft2017]

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.

[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!

[mjtaft2017]

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

[rittermd]

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.

[mjtaft2017]

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

[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