Expression Help.............!!!

[#1]

Dear All,

One of my  simple expression i have used in my pivot table is shown below. 

ROUND (RANGESUM (BRO_COMM,AGE_COM,+GRO_COM,-TOT_COM))

I want to hide or remove  the zero values shown as a result of above expression without considering the results of the other expression.

So can you any one help me to find the effective formula instead of the above or what modification i could do.

Thanks.

Priyantha.

Sample Attached herewith.

[awhitfield]

Hi,

you were nearly there

If(ROUND(RANGESUM(BRO_COMM,AGE_COM,+GRO_COM,-TOT_COM))<>0,ROUND (RANGESUM (BRO_COMM,AGE_COM,+GRO_COM,-TOT_COM)),'')

Andy

[sunny_talwar]

Please check if this is what you are trying to do? PFA

Best,

Sunny

[#4]

Hi Andrew,

Your solution removes the zero value of the table instead of removing the relevant raw from the table.

Priyantha.

[awhitfield]

Sorry,

but that is what your question said:

'I want to hide or remove  the zero values shown as a result of above expression without considering the results of the other expression.'

[#6]

Really i made a mistake..

I'm so sorry my friend...

Priyantha.

[sunny_talwar]

Use following expressions:

1. =If(not IsNull(EXCESS), SUM(COM_PRE+SR+TC))
2. =If(not IsNull(EXCESS), SUM(IF(CAT='BR',(COM_PRE*BR_COM+SR*BR_RS+TC*BR_TC)/100,0)))
3. =If(not IsNull(EXCESS), SUM(IF(CAT='AG',(COM_PRE*AG_COM+SR*AG_RS+TC*AG_TC)/100)))
4. =If(not IsNull(EXCESS), SUM(IF(CAT='GR',(COM_PRE*11+SR*11+TC*11)/100)))
5. =If(not IsNull(EXCESS), SUM(CRE))

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM+SR*BR_RS+TC*BR_TC)/100,0)),

   SUM(IF(CAT='AG',(COM_PRE*AG_COM+SR*AG_RS+TC*AG_TC)/100)),

   SUM(IF(CAT='GR',(COM_PRE*11+SR*11+TC*11)/100)),

  -SUM(CRE))) <> 0,

   ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM+SR*BR_RS+TC*BR_TC)/100,0)),

   SUM(IF(CAT='AG',(COM_PRE*AG_COM+SR*AG_RS+TC*AG_TC)/100)),

   SUM(IF(CAT='GR',(COM_PRE*11+SR*11+TC*11)/100)),

  -SUM(CRE))))

HTH

Best,

Sunny

[#8]

Same results.........!!!

Anyway Thanks a lot for the help extended..

Priyantha.

[sunny_talwar]

Is this not the output you are looking for???

[#10]

I have used your formula a an expression ,

It can not be used from the beginning. so i used the following part only.

"

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM+SR*BR_RS+TC*BR_TC)/100,0)),

   SUM(IF(CAT='AG',(COM_PRE*AG_COM+SR*AG_RS+TC*AG_TC)/100)),

   SUM(IF(CAT='GR',(COM_PRE*11+SR*11+TC*11)/100)),

  -SUM(CRE))) <> 0,

   ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM+SR*BR_RS+TC*BR_TC)/100,0)),

   SUM(IF(CAT='AG',(COM_PRE*AG_COM+SR*AG_RS+TC*AG_TC)/100)),

   SUM(IF(CAT='GR',(COM_PRE*11+SR*11+TC*11)/100)),

  -SUM(CRE)))) "

Any thing wrong i have dine???

Priyantha.