topic Re: Weird aggr function behavior in QlikView

Weird aggr function behavior

inescastelhano — Mon, 10 Aug 2015 11:12:52 GMT

Hey there!

I am working on some project, and I want the expression to return '01:00:00' if "Hour beginning"<8 and "Hour end">8. Can't be done with an if condition because this expression will be part of a sum.

I have the following table, and my result is in the last column:

I have this expression:

aggr(sum({<[Hour A]={"<8"}, [Hour B]={">8"}>} '01:00:00'), [ID])

I don't understand why, but sometimes it randomly returns the wrong value... It was supposed to be '01:00:00' for all the table rows, but I randomly get other values.

Does anybody have a clue on what to do with this?

Thanks in advance!

Cheers,

Ines

Re: Weird aggr function behavior

sunny_talwar — Mon, 10 Aug 2015 11:23:55 GMT

Can you add another expression to you chart: Count(ID) to check if 20019528 is occurring twice in your data by chance.

Re: Weird aggr function behavior

Gysbert_Wassenaar — Mon, 10 Aug 2015 11:28:03 GMT

Perhaps you have several records per ID value that meet those conditions. Try adding this expression to the chart to check: count({<[Hour A]={"<8"}, [Hour B]={">8"}>}[Hour A])

Re: Weird aggr function behavior

rubenmarin — Mon, 10 Aug 2015 12:12:46 GMT

You can use Avg() instead of Sum():

aggr(avg({<[Hour A]={"<8"}, [Hour B]={">8"}>} '01:00:00'), [ID])

Re: Weird aggr function behavior

inescastelhano — Mon, 10 Aug 2015 13:37:48 GMT

Yes, that's correct. That was my mistake. Thanks for your help

Re: Weird aggr function behavior

inescastelhano — Mon, 10 Aug 2015 13:38:10 GMT

Thank you, it solved the problem!

Re: Weird aggr function behavior

inescastelhano — Mon, 10 Aug 2015 13:38:50 GMT

Yes, I do. Actually I think I may have an error in my data, I have to check. Thanks