Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Strange Chart for Expression

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.

1 Solution

Accepted Solutions

Re: Strange Chart for Expression

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

5 Replies

Re: Strange Chart for Expression

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


talk is cheap, supply exceeds demand
Not applicable

Re: Strange Chart for Expression

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?

Not applicable

Re: Strange Chart for Expression

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

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

Not applicable

Re: Strange Chart for Expression

It seems like it worked. Thanks!

Community Browser