2 Replies Latest reply: Oct 15, 2015 2:45 PM by Oleg Troyansky RSS

    Question from a new guy

      Hello all,

       

      I am new to Qlik Desktop so please forgive my lack of knowledge. I do however have a fair amount of BI experience so I know something of what I am trying to achieve.

       

      I am setting up a dashboard for directors in my company and want to create a KPI which divides one KPI by another KPI.

       

      I have 2 KPI's on the sheet which are:

       

      1. Total Analysis Hours - KPI is a calculated measure using the following sum(if Type ='Sample Analysis',Hours))

      2. Total Samples Analysed -  sum(Samples Analysed)

       

      I want to be able to divide the total analysed by the hours in order to provide a average time it has taken to analyse a sample.

       

      How can this be done easily? Please feel free to suggest a totally new approach if the above is wrong.

       

      Thanks!

       

      Kevin.

        • Re: Question from a new guy
          Massimo Grossi

          maybe

           

          sum(if Type ='Sample Analysis',Hours))

          /

          sum(Samples Analysed)     (or count, count distinct?)

          • Re: Question from a new guy
            Oleg Troyansky

            Hi Kevin,

             

            if you are looking for a way to divide one Measure (as a Master Item) by another measure, then I don't think it's possible to do... Basically, you have two choices:

             

            1. Good ol' copy and paste:

                 sum(Samples Analysed) / sum(if Type ='Sample Analysis',Hours))

                 (the syntax is a bit incorrect, but I preserved your original spelling)


            2. Using variables. You can assign variables in the data load script and then re-use the existing variables to divide one by another. Something like this:


            SET exp_Hours = sum(if Type ='Sample Analysis',Hours));

            SET exp_SamplesAnalysed = sum(Samples Analysed);


            In the UI, you can use the following as Measures:


            Hours:    $(exp_Hours)

            Samples: $(exp_Samples)

            Avg:        $(exp_Samples)/$(exp_Hours)


            check out my new book QlikView Your Business - you will learn more about using variables to store your calculations and a lot more advanced stuff about QlikView and Qlik Sense.


            cheers,

            Oleg Troyansky

            QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense