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

[PrashantSangle]

Hi,

Please clear me below points,

when Your Complexity is 0 then you dont want sum your hrs

or

when Your Hrs is 0 then you dont want sum your Complexity

or

you want to check both condition.

Clear Flag according your requirement.

In above script

1:if(Sum(Complexity)=0,'Zero','Non-Zero') as flag

for

2:if(Sum(hrs)=0,'Zero','Non-Zero') as flag

for

3:

if(Sum(Complexity)=0 or sum(hrs)=0,'Zero','Non-Zero') as flag

and used flag in both expression

Try like

sum({<flag={'Non-Zero'}>} Complexity)/sum({<flag={'Non-Zero'}>}Total Hrs)

Regards,

[Chanty4u]

yeah sunny it is wrkng in sample app but nt in my qvw

=Sum({<desc={' Design - SIT',' Design - RT' -= {0}>}Complexity)/

Sum(TOTAL{<desc={'Test - Design - SIT','Test - Design - RT'}>}hrs)

its gvng error

[Chanty4u]

when  Hrs is 0 then i dont want sum your Complexity .

and that hrs cme frm both

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

[sunny_talwar]

What error is it giving? Can you post a screenshot?

[Chanty4u]

chk dis

[sunny_talwar]

Are you sure you have a field named desc or it is called something else (may be like description or Desc)? Seeing your expression, the error seems to begin from desc and that is why I would first check if that is the field name available in your database or not.

[Chanty4u]

yes sunny all the field names are avai;able in my db

[sunny_talwar]

Also you haven't set hrs -= {0}. May be try this:

=Sum({<desc={' Design - SIT',' Design - RT', hrs -= {0}>}Complexity)/

Sum(TOTAL{<desc={'Test - Design - SIT','Test - Design - RT'}>}hrs)

[Chanty4u]

error gvng

-= {0}>}   dis part sunny

=Sum({<desc={' Design - SIT',' Design - RT' -= {0}>}Complexity)/

Sum(TOTAL{<desc={'Test - Design - SIT','Test - Design - RT'}>}hrs)

[PrashantSangle]

Hi,

For that in above solution see suggestion 2 of Creating Flag.

If you want the solution at front end then you can use indirect set analysis inside your expression.

Also If it will not satisfied your requirement, then Post sample data in Excel file with required output in another sheet.

Note : I am working Personal edition therefore I can not open any other qvw.

Regards