Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
maybe
sum(if Type ='Sample Analysis',Hours))
/
sum(Samples Analysed) (or count, count distinct?)
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