Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Where in expressions

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

1 Solution

Accepted Solutions
shay_cooper
New Contributor III

Where in expressions

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

8 Replies
shay_cooper
New Contributor III

Where in expressions

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

Not applicable

Where in expressions

Brilliant, the second one worked

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

Thanks

shay_cooper
New Contributor III

Where in expressions

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.

Not applicable

Where in expressions

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

shay_cooper
New Contributor III

Where in expressions

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

Not applicable

Where in expressions

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.

shay_cooper
New Contributor III

Where in expressions

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.

Not applicable

Where in expressions

That has worked a treat

Thanks

V. happy

Community Browser