Where in expressions

[#1]

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

[Anonymous · Accepted solution]

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

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

or

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

Hope that helps

[#3]

Brilliant, the second one worked

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

Thanks

[Anonymous]

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.

[#5]

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

[Anonymous]

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

[#7]

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.

[Anonymous]

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.

[#9]

That has worked a treat

Thanks

V. happy