8 Replies Latest reply: Jun 28, 2011 12:06 PM by melaniehuggins RSS

    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

        • Where in expressions
          Shay Cooper

          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

            • Where in expressions

              Brilliant, the second one worked

               

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

               

              Thanks

                • Where in expressions
                  Shay Cooper

                  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

                      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
                          Shay Cooper

                          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