SUM field if respect a condition?

[#1]

Hi ,

I need to create an expression that is the sum of WORK TIME of the machine and AVAIBLE TIME of the MACHINE.

Code Causal	Causal Description	Time (H)
210	WORK	1
505	Broken Machine	2
300	Machine OFF	7
504	Maintenance Machine	3
210	WORK	2

• The "Available Time "  is the sum of Duration without the row with "code causal" =300

         My expression is :

         Available_Time = if( Code Causal<>300,Sum(Time),0)

• The "Work Time "  is the sum of Duration with only the row "code causal" =210

        My expression is :

        Work_Time = if( Code Causal=210,Sum(Time),0)

But for the Work time the expression doesn't work, no result.

After i would use these expressions to create one KPI, that is the percert of Work Time / Available_Time.

Could you help me?

Thanks a lot

Fede

[oknotsen]

I suggest using something we call "Set Analysis" instead of IF statements.

For Work_Time:

sum({< [Code Casual] = {210} > } Time)

For Available_Time:

sum({< [Code Casual] -= {300} > } Time)

[sunny_talwar · Accepted solution]

Try it with set analysis instead:

Available_Time = Sum({<Code Causal ={'*'} - {300}>} Time)

Work_Time = Sum({<Code Causal ={200}>} Time)

[#4]

Thanks Sunny!!!

It works fine!!!

[oknotsen]

Even with the space in the name "Code Causal"? That is a surprise to me.

[sunny_talwar]

Hahahaha I think he must have added it in his code