Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Brilliant, the second one worked
can you explain how that works and why I need the IncidentType at the end
Thanks
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.
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
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
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.
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.
That has worked a treat
Thanks
V. happy