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.

[sunny_talwar]

You need to use all the expressions I gave you in order to achieve what you are trying to do. Alternatively you can use a calculated dimension which forces the dimension to be null when your last expression is null (and then you can use suppress null values in the dimension).

Not sure which method you want to go with.

Best,

Sunny

[sunny_talwar]

If you have licensed version of QlikView then, do take a look at the attached qvw file.

[#4]

Dear Sunindia,

please help me as i'm very new to qv.

I have used your formula in expression 'EXCESS'. as whole formula can not be used there i have used the part of it.

Alternatively where i want to use this?

Priyantha.

[sunny_talwar · Accepted solution]

This is what you need to do:

You have 6 expressions in your tables, you need to replace all of them.

Expression 1 needs to be: =If(not IsNull(EXCESS), SUM(COM_PRE+SR+TC)) instead of SUM(COM_PRE+SR+TC)

Expression 2 needs to be: =If(not IsNull(EXCESS), SUM(IF(CAT='BR',(COM_PRE*BR_COM+SR*BR_RS+TC*BR_TC)/100,0))) instead of SUM(IF(CAT='BR',(COM_PRE*BR_COM+SR*BR_RS+TC*BR_TC)/100,0))

Expression 3 needs to be: =If(not IsNull(EXCESS), SUM(IF(CAT='AG',(COM_PRE*AG_COM+SR*AG_RS+TC*AG_TC)/100))) instead of SUM(IF(CAT='AG',(COM_PRE*AG_COM+SR*AG_RS+TC*AG_TC)/100))

Expression 4 needs to be: =If(not IsNull(EXCESS), SUM(IF(CAT='GR',(COM_PRE*11+SR*11+TC*11)/100))) instead of SUM(IF(CAT='GR',(COM_PRE*11+SR*11+TC*11)/100))

Expression 5 needs to be: =If(not IsNull(EXCESS), SUM(CRE)) instead of SUM(CRE)

and finally your Excess expression

Expression 6 needs to be: 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))))

I hope you will understand what needs to be done.

Best,

Sunny

[#6]

Thank you So Much...

[sunny_talwar]

Did it finally work???

[#8]

Really.........!!!

gr8 help.

Thanks once again.

Priyantha.

[#9]

Dear Sunindia,

In addition to that,

If I want to show only the negative balances in the 'EXCESS' column what should be the modification i must do in the suggested expression?

Regards,

Priyantha.

[sunny_talwar]

You will need to change only your last expression to this:

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))))