calculated Dimensions in a chart

[#1]

Hi all

I am new to Qlikview and am awaiting the training courses, have picked up quite a lot already however I need to produce the following chart.

the data is simple just inline loaded with Age and Gender.

the data is simple just inline loaded with Age and Gender.

LOAD

* INLINE [

Age, Gender

32,Male

33,Female

0,Male

etc

0 counts for Unknown.

I thought that using calculated dimensions would work but cannot get this to work.

Any thoughts

[prabhu0505]

Hi wayne,

Using Calculated dimensions you can decide whether to display a particular value(of dimension) and its row.

Example:

=if(isnull(Owner1), #, #)

Assume:

Owner1 is some field in table and also it is a list box in qlikview document

# - some unique number for each record

Result :

Selection of Owner1 has no effect in the chart.

[#3]

Ok I have attached where I am So far, its close

However, there is an issue with the chart as to work out the percentage it is currently selecting the total count for male and female, you can see this as the chart figures change if you just selct male for example.

I have managed to work this out in the tables and this is correct however I cannot get this to work for the chart.

Also when I select the values on axis radio button it overwrites male and female and is not readable.

any thoughts

[johnw]

To fix your %, you just had total(Age) where you need total Age.

count(Age)/count(total Age)

I highly recommend calculating the age range once, during the script, rather than every time you use the chart. There are several approaches, but for something this simple, I'd just use nested IFs:

if(Age<20,dual('<20',1)
if(Age<30,dual('20-29',20)
if(Age<40,dual('30-39',30)
if(Age<50,dual('40-49',40)
if(Age<60,dual('50-59',50)
,dual('60+',60)))))) as "Age Range"

I suppose it doesn't save you much if you're going to have separate expressions in the chart, but generally speaking, you'd then just do a pivot table with Age Range as one of the dimensions. Probably won't let you do exactly what you want for your other columns, though.

Not sure what you're talking about regarding the "axis radio button".

[#5]

John thanks for replying

What I need to happen for each line in the chart is the percentage only for that gender when displaying both, What is happening is that when both genders are selected the percentage is worked out over the whole count of both genders. To prove this if you select female only the percentages jump up to what they should be in the table.

Any ideas

Thanks

Wayne

[johnw]

On the expressions tab, remove the checkmark next to "relative" and use count(Sex)/count(total <Sex> Sex). The "relative" checkbox is always relative to the selected data set, so isn't distinguishing between genders. Count(total Sex) does the same thing. But Count(total <Sex> Sex) tells it we want the count, but for each sex separately. So then we can manually calculate the correct percentage.