Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

5 Replies
Gysbert_Wassenaar

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
Author

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
Author

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?

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

It seems like it worked. Thanks!