topic Re: Strange Chart for Expression in QlikView

Strange Chart for Expression

Fri, 08 Mar 2013 10:23:00 GMT

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.

Re: Strange Chart for Expression

Gysbert_Wassenaar — Fri, 08 Mar 2013 10:42:26 GMT

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).

Re: Strange Chart for Expression

Fri, 08 Mar 2013 10:47:35 GMT

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?

Re: Strange Chart for Expression

Fri, 08 Mar 2013 12:52:37 GMT

Found distinct but apparently I cant use it like this:

sum(
(sum (mid((DISTINCT end),10,2)))
-
(sum (mid((DISTINCT begin),10,2)))
)

Any hints?

Re: Strange Chart for Expression

kaushiknsolanki — Fri, 08 Mar 2013 13:00:15 GMT

Try this,

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.

Regards,

Kaushik Solanki

Re: Strange Chart for Expression

Fri, 08 Mar 2013 13:25:38 GMT

It seems like it worked. Thanks!