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,

try one more solution.

Calculate your sum(Complexity) expression in back end and also create one flag

which indicates your sum(Complexity) contain 0 value or other than 0 value.

and use that flag in your front end denominator calculation.

for example if you create flag in script which show zero and non-zero value.

then your front end expression become.

sum(Complexity) / Sum({<desc={'Design - IT',' Design - RT'},flag={'non-zero'}>} TOTAL hrs)

Regards,

[PrashantSangle]

Hi,

for example :

test:

LOAD * INLINE [

    Desc, Complexity, Hrs

    a, 12, 23

    b, 0, 34

    c, 23, 23

    d, 45, 34

];

Left Join

load Desc,

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

resident test

group by Desc;

Then create straight table in front end

add Desc as dimension

expression :

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

Regards,

[jagan]

Hi,

Check this expressions separately and verify whether they are giving correct answers. 

Also, 0 is not equal to 0.00044343 something?  Are you missing this condition?

Regards,

jagan.

[Chanty4u]

in this  0 is the  effort......  so if  effort is  ' 0'    there is no cmplxcity ryt..

[jagan]

Hi Suresh,

It is very difficult to provide the solution without the document, all give answers based on the assumptions only.  But you never get the correct Answer.  To get the answer instantly prepare some sample data or attach some sample and let us know your expected output.

Regards,

Jagan.

[Chanty4u]

we are nt using in  stright table......we are usng in  text object

[PrashantSangle]

Hi,

It is working in text object also.

Check value of

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

In Text Object it is giving 80 as 23+23+34 which is not considering 34 hrs of Desc - b.

Regards,

[Chanty4u]

hi  max,

PFA

thnx fr the reply....

exp it is nt wrkng

bcz

LOAD * INLINE [

    Desc, Complexity, Hrs

    a, 12, 23

    b, 0, 34

  c, 23, 0

    d, 45, 34

];

where  hr='0'  that will nt caluculate in complexcity sum

means.

i need  sum(comp) as  12+0+45  only  /34+34+23

[Chanty4u]

i dnt want non zero correspdng values in hrs .

means  where 0 in hrs   there is no compxcity ryt..

[sunny_talwar]

Try this:

=Sum({<Hrs -= {0}>}Complexity)