Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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!
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
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.
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
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