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.





        • Re: Question from a new guy
          Massimo Grossi



          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.


            Oleg Troyansky

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