Creating a dynamic KPI based on timestamp calculations
Hi!
I'm new to Qlik and are at the moment working with a quite complex script where I want to measure time between two timestamps taking into consideration weekends, working hours and holidays etc. The main part of the expression I found here on the community and it's working fine. However, I've made two master measures of it, one just performing the calculations and one which are averaging the calculations.
Below is the script I'm using for the first master measure, and the second master measure is the same but adding avg(:
if(NetWorkDays(Datum_Skapad,Datum_Skapad,[Helgdag]) and floor(Datum_Skapad)=floor([Datum_Stängd]),-MakeTime(9))
)
)
What I want to do now is to to create a KPI which counts all the instances in my dataset where the output by the first master measure is above the averaged master measure. I believe I've gotten it to work when writing the average as a static timestamp, like ">=07:28" where 07:28 is the average of the first master measure. However, I want the KPI to be dynamic so that when new data is added and the average is changed, the KPI is responsive to such a change and corrects itself. At the moment, the expression for the KPI looks like this:
if(NetWorkDays(Datum_Skapad,Datum_Skapad,[Helgdag]) and floor(Datum_Skapad)=floor([Datum_Stängd]),-MakeTime(9))
)
)>= '07:28, 1, 0))
Any Suggestions or thoughts about this? For some reason I can't replace the '07:28' part of the expression with my second master measure. And also, the SUM function doesn't seem to be correct here even if the KPI do give me a number.
