Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Expressions and Dimensions in Bar Charts

Hi,

I am very new to qlikview. I have a very straightforward excel data file that I would like to analyse, example below:

CountryRegionAge (0-20 years)Age (21-40)Age (41 +)
EnglandYorkshire10%20%70%
EnglandCounty Durham5%30%65%
ScotlandFife10%40%50%
Northern IrelandBelfast20%20%60%

I would like to create a bar chart with the age variables (last 3 columns on the right) as the X axis and the percentage data as the values in the chart (y axis 0% - 100%). I loaded the data into qlikview in a standard way and created 3 seperate expressions for the Age Bands (which can be seen in the legend). I then used list boxes for country and region so that these can be selected and the bar chart changes - lovely. A couple of questions:

  1. In this example do I need a dimension?
  2. How can I make the Age categories in the above example appear in the x axis labels?

Thanks

1 Solution

Accepted Solutions

Re: Expressions and Dimensions in Bar Charts

In your script, do the following:

TableName:

CrossTable(Age, Proportion, 2)

LOAD Country

          Region

          Age (0-20 years)

          Age (21-40)

          Age (41+)Age (0-A20 yeaAs)

FROM YourFile.csv;

7 Replies
Not applicable

Re: Expressions and Dimensions in Bar Charts

Hope this helps!

class( expression, interval [ , label [ , offset ]] )

Creates a classification of expressions. The bin width is determined

by the number set as interval. The result is shown as a<=x<b, where

a and b are the upper and lower limits of the bin. The x can be

replaced by an arbitrary string stated in label. 0 is normally the

default starting point of the classification. This can be changed by

adding an offset.

Examples:

class( var,10 ) with var = 23 returns '20<=x<30'

class( var,5,'value' ) with var = 23 returns '20<= value <25'

class( var,10,'x',5 ) with var = 23 returns '15<=x<25'

Not applicable

Re: Expressions and Dimensions in Bar Charts

Sorry, I suspect I have made the mistake of trying to make this very complicated and the actual  issue is the way I am loading the table. How do I make the above excel flat table load into a long thin Qlikview table that would look something like this (begining of table shown as example)?

CountryRegionAgeProportion
EnglandYorkshire0-2010%
EnglandYorkshire21-4020%
EnglandYorkshire41+70%
EnglandCounty Durham0-205%
EnglandCounty Durham21-4030%
EnglandCounty Durham41+65%




So the 3 age columns become a new column and the data is all placed into the final new end column?

Thanks,

Re: Expressions and Dimensions in Bar Charts

In your script, do the following:

TableName:

CrossTable(Age, Proportion, 2)

LOAD Country

          Region

          Age (0-20 years)

          Age (21-40)

          Age (41+)Age (0-A20 yeaAs)

FROM YourFile.csv;

Not applicable

Re: Expressions and Dimensions in Bar Charts

Please do it as suggested by Nicole! That will work out...

Not applicable

Re: Expressions and Dimensions in Bar Charts

Thanks folks, that is really haelpful and works!

I just need to work out exactly what has happened in the following line.

CrossTable(Age, Proportion, 2)

Age and Proportion are the new variables. What exactly is the number 2 doing, To my mind it takes every variable after the first two and pivoting the data into the new format is this correct and is there a better explanation?


Re: Expressions and Dimensions in Bar Charts

What you're thinking is correct.  The 2 means keep the first 2 columns the way that they are, and then pivot the rest of the columns into two new columns named Age and Proportion.

Not applicable

Re: Expressions and Dimensions in Bar Charts

Thanks, that is a much clearer way of putting it. I now have a crosstable \ fill question but will put this in a new discussion.

Community Browser