Re:Sum and exclude '"0"

[Chanty4u]

hi all,

I have expression lik below

=num(

sum(Complexity)

/

(11111

sum({<desc={'Design - IT',' Design - RT'}>}hrs)

/8

)

,'##.##')

i used same formualue in   straitght table  nummerator and denomenator.

am getting the sum of abve /sum of below exp.

My req is---   where  sum({<desc={'Design - IT',' Design - RT'}>}hrs)    this value is '0' shwn in chart  that corresponding values shud nt count and  remaing shud be count / total of sum({<desc={'Design - IT',' Design - RT'}>}hrs) .

example:  there is 47 value.. corresponig 172...   the result shud be   172/408.  is my result.

thnks in advance

[Chanty4u]

hi jagan,

My expected out put in text object is:

3.887617066

[jagan]

Hi,

Try like this

=Sum(Aggr(If(sum({<cl_ktlo_desc={'Test - Design - SIT','Test - Design - RT'}>}cl_ktlo_hrs) > 0, sum(Test_Complexity), 0), Dim1, Dim2)) /

sum({<cl_ktlo_desc={'Test - Design - SIT','Test - Design - RT'}>}cl_ktlo_hrs)

Replace Dim1 & Dim2 with your actual dimensions.

Regards,

jagan.

[jagan · Accepted solution]

Final expression is

=Sum(Aggr(If(sum({<cl_ktlo_desc={'Test - Design - SIT','Test - Design - RT'}>}cl_ktlo_hrs) > 0, sum(Test_Complexity), 0), Project, Period)) /

sum({<cl_ktlo_desc={'Test - Design - SIT','Test - Design - RT'}>}cl_ktlo_hrs)

[Chanty4u]

Ok.let me try and update u bro..thnx

[jagan]

Close this thread if you got the Answer.

Regards,

jagan.

[jagan]

If you are not able to get correct value always try using the Aggr(), then it will calculate the final value based on the dimensions wise.

Regards,

Jagan.

[Chanty4u]

sure jagan.Thnx fr info.

[Anonymous]

sum({<desc={'Design - IT',' Design - RT'}-e{desc={'0'}}>}hrs) 

try the above expression