Average time with two criteria met

[#1]

Hi all,

How would I correct the following to allow the expression to show the average time wher the clock_start_to_first_assign<0 and Performance_Category='Red8'? it just shows a '-' in the cell instead of a time value. If I take out the IF(cloc..... part then it does work. I have tried set analysis but I don't think it was given me the truetime. Is this even possible in SA?

IF(clock_start_to_first_assign<0 and Performance_Category='Red8',

num(FABS(DIV(avg(clock_start_to_first_assign), 3600)),'#,#00')
&':'&
num(FABS(DIV(FMOD(avg(clock_start_to_first_assign),3600), 60)), '00')
&':'&
num(FABS(FMOD(avg(clock_start_to_first_assign),60)),'#,#00')
)

Thanks in advance for your help.

[swuehl]

I believe you should embed your if() statement into the aggregation, not the other way round (or use set analysis).

I think you can also simplify your time formatting, maybe like

=interval( avg( {<clock_start_to_first_assign = {"<0"}, Performance_Category= {Red8} >} clock_start_to_first_assign), 'hh:mm:ss')

[#3]

Hi Swuehl, thank you for the quick reply. It does work and I just need to validate the information now. Many thanks.

[#4]

Phil:

Its defintly possible with Set Analysis. Make sure that for the conditions there are fields.

For converting the expresion to time, you can use interval function. So if the data exists for the conditions your the following would give you what you are lookin for

Interval(avg({<clock_start_to_first_assign={"<0"},Performance_category={'Red8'}>} clock_start_to_first_assign),'D hh:mm')

Regards,

Kiran.