Weird aggr function behavior

[inescastelhano]

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

[sunny_talwar]

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

[Gysbert_Wassenaar]

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

[rubenmarin · Accepted solution]

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

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

[inescastelhano]

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

[inescastelhano]

Thank you, it solved the problem!

[inescastelhano]

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