topic Where in expressions in QlikView

Where in expressions

Tue, 28 Jun 2011 14:23:12 GMT

Hi I'm new to Qlikview so please be gentle

Basically I have loaded an excel spreadheet with the following columns

Region

Incident Type

In Incident type I have things like RTA, Injury, Complaint etc.

Now I want to write an expression that counts the incident type but only where it equals Injury

so I trying this

Count (IncidentType)

Where (IncidentType) Like '%Injury%'

but it doesn't work?

Help?

Thanks

Where in expressions

Anonymous — Tue, 28 Jun 2011 14:29:02 GMT

Hi there you would want an IF statment or Set Analysis, either options below should work for you;

sum(if(IncidentType='Injury',1))

count({<IncidentType={'Injury'}>}IncidentType)

Hope that helps

Where in expressions

Tue, 28 Jun 2011 14:41:29 GMT

Brilliant, the second one worked

can you explain how that works and why I need the IncidentType at the end

Thanks

Where in expressions

Anonymous — Tue, 28 Jun 2011 14:45:13 GMT

Yeah sure no problem, what the second option did was called set analysis, this basically tells qlikview that you want to filter the data as if the user had made a selection.

In your case it will count all of the instances of Incident Type as if you yourself had clicked on Injury within the IncidentType field.

Where in expressions

Tue, 28 Jun 2011 15:20:40 GMT

Ok so now I need to add to the expression so it filters on certain regions

so something like

count({<IncidentType={'Injury'}>}IncidentType) where region = North

basically I'm trying to work out the following for each region

No of injuries / Hours per month x 1,000,000

and present as a bar chart

Thanks

Where in expressions

Anonymous — Tue, 28 Jun 2011 15:32:26 GMT

Hi Mel

You would want to update the code as follows;

count({<IncidentType={'Injury'},Region={'North'}>}IncidentType)

Please note that for each bar you would want showing you need to repeat this changing to the appropriate region as below for example;

count({<IncidentType={'Injury'},Region={'North'}>}IncidentType) -North Bar

count({<IncidentType={'Injury'},Region={'South'}>}IncidentType)- South Bar

However you should be able to use the original code and set Region as a dimension as such each region will have the expression calculated anyway.

(count({<IncidentType={'Injury'}>}IncidentType)/Hours per month)*1000000

Where in expressions

Tue, 28 Jun 2011 15:45:57 GMT

Thanks

I've got hours per month for each region as a variable as they change each month, so that why I was thinking that I needed to do this for each bar.

Where in expressions

Anonymous — Tue, 28 Jun 2011 15:50:29 GMT

You should be able to try;

(count({<IncidentType={'Injury'}>}IncidentType)/$(VarName))*1000000

Where VarName is replaced by the name of the variable holding the number of hours per month.

This should work as a standard expression which is calculated over the dimension Region, you shouldnt have any problems.

Where in expressions

Tue, 28 Jun 2011 16:06:26 GMT

That has worked a treat

Thanks

V. happy