topic Re: Expression Help.............!!! in QlikView

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

Thu, 23 Apr 2015 10:04:33 GMT

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.

Re: Expression Help.............!!!

awhitfield — Thu, 23 Apr 2015 10:15:18 GMT

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

Re: Expression Help.............!!!

sunny_talwar — Thu, 23 Apr 2015 10:18:56 GMT

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

Best,

Sunny

Re: Expression Help.............!!!

Thu, 23 Apr 2015 10:22:16 GMT

Hi Andrew,

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

Priyantha.

Re: Expression Help.............!!!

awhitfield — Thu, 23 Apr 2015 10:25:25 GMT

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.'

Re: Expression Help.............!!!

Thu, 23 Apr 2015 10:29:26 GMT

Really i made a mistake..

I'm so sorry my friend...

Priyantha.

Re: Expression Help.............!!!

sunny_talwar — Thu, 23 Apr 2015 10:36:12 GMT

Use following expressions:

=If(not IsNull(EXCESS), SUM(COM_PRE+SR+TC))
=If(not IsNull(EXCESS), SUM(IF(CAT='BR',(COM_PRE*BR_COM+SR*BR_RS+TC*BR_TC)/100,0)))
=If(not IsNull(EXCESS), SUM(IF(CAT='AG',(COM_PRE*AG_COM+SR*AG_RS+TC*AG_TC)/100)))
=If(not IsNull(EXCESS), SUM(IF(CAT='GR',(COM_PRE*11+SR*11+TC*11)/100)))
=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

Re: Expression Help.............!!!

Thu, 23 Apr 2015 10:45:24 GMT

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

Anyway Thanks a lot for the help extended..

Priyantha.

Re: Expression Help.............!!!

sunny_talwar — Thu, 23 Apr 2015 10:46:57 GMT

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

Re: Expression Help.............!!!

Thu, 23 Apr 2015 10:56:36 GMT

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.

Re: Expression Help.............!!!

sunny_talwar — Thu, 23 Apr 2015 10:58:51 GMT

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

Re: Expression Help.............!!!

sunny_talwar — Thu, 23 Apr 2015 11:02:37 GMT

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

Re: Expression Help.............!!!

Thu, 23 Apr 2015 11:15:53 GMT

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.

Re: Expression Help.............!!!

sunny_talwar — Thu, 23 Apr 2015 11:26:43 GMT

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

Re: Expression Help.............!!!

Thu, 23 Apr 2015 11:31:07 GMT

Thank you So Much...

Re: Expression Help.............!!!

sunny_talwar — Thu, 23 Apr 2015 11:31:50 GMT

Did it finally work???

Re: Expression Help.............!!!

Thu, 23 Apr 2015 11:40:33 GMT

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

gr8 help.

Thanks once again.

Priyantha.

Re: Expression Help.............!!!

Thu, 23 Apr 2015 12:02:18 GMT

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.

Re: Expression Help.............!!!

sunny_talwar — Thu, 23 Apr 2015 12:20:06 GMT

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