Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Chanty4u
MVP
MVP

Re:Sum and exclude '"0"

hi all,

I have expression lik below

=num(

sum(Complexity)

/

(11111

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

/8

)

,'##.##')

exclude.png

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

87 Replies
sunny_talwar

May be something along these lines:

If(Sum({<desc={'Design - IT',' Design - RT'}>}hrs) > 0 and Sum(Complexity) > 0, Sum(Complexity)/Sum(TOTAL {<desc={'Design - IT',' Design - RT'}>}hrs))

and assuming you are doing this in a Straight table, you can set the total mode on expression tab to sum of rows.

Chanty4u
MVP
MVP
Author

thnx hirish...

but  i need  example:   where zeros are there we dnt want to caluculate

sum(com)   sum(hrs)

12               11

----0                    12

----11               0

20               10

now     sum(comp)   20+12 /11+12+10

i need dis

Chanty4u
MVP
MVP
Author

but  i need  example:   where zeros are there we dnt want to caluculate

sum(com)   sum(hrs)

12               11

----0                    12

----11               0

20               10

now     sum(comp)   20+12 /11+12+10

i need dis

sunny_talwar

Not sure I understand, lets look at an example:

Dim1,     Exp1,     Exp2,     ExpNeeded

A,           12,         11,          ?

B,           0,           12,          ?

C,           11,          0,           ?

D,           20,          10,          ?

TOTAL     ?            ?            ?

Anonymous
Not applicable

=sum({1-$<columnname-={0}>}Sales)

Chanty4u
MVP
MVP
Author

exp1

12+0+20

total=32

exp2=11+12+0+10

total =32

where exp2 value is 0...that  crspndng  exp1 value nt to be added

Chanty4u
MVP
MVP
Author

for ua all expr same value shwwng sunny

Anonymous
Not applicable

The logic shared by you it should be:

exp2: 11+12+0+10 = 21

instead of shared by you

exp2=11+12+0+10

total =32

21 0r 32 ??

Chanty4u
MVP
MVP
Author

that  is frst user want...now it chnged bro..

Chanty4u
MVP
MVP
Author

the exp2 is  num of hurs wrked.....and that  cmpxity shwng in exp1..

so if exp2 is 0 means  .num of hours is zero...den dere is no complxty  so.....avoid that sum