Discussion Board for collaboration related to Creating Analytics for QlikView.
Hi. I have one listBox for startTimes and one listBox for endTimes in the format: "yyyymmddTHHmmss".
I am using the "mid()" Expression function to only retrieve the hours, "HH" from this string and show the total hours in a Chart.
But it seems like I do something wrong because the result of the chart should be:
15-13 + 15-13 = 4
But of some reason it shows the result as 48. Any ideas why?
Attached the listboxes and chart.
Go to Solution.
Sum(Aggr(mid(End,10,2) - mid(Begin,10,2),End,Begin))
I am not sure this will work properly, but try and chek out.
My guess is you have more than one record with the same begin and end date. The difference is calculated for every row and then summed (over all relevant rows).
Now when you say it I realise that you found the problem! Question now is how do I slove this kind of problem? Can I somehow use "distinct" or somthing similar for Expressions?
Found distinct but apparently I cant use it like this:
sum( (sum (mid((DISTINCT end),10,2))) - (sum (mid((DISTINCT begin),10,2))) )
It seems like it worked. Thanks!