topic Re: Expression Error. in QlikView

Expression Error.

Tue, 12 May 2015 07:02:33 GMT

Dear Experts,

Please Help me to find the error in following Expression.

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(RANGESUM(BRO_ORC+AGE_ORC+GRO_ORC=0) and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))) <0,

ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100)),

SUM(IF(BRO_ORC=0 and AGE_ORC=0 and GRO_ORC=0 and ORC_COM>0,ALT(SUM(COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))))

Re: Expression Error.

Tue, 12 May 2015 07:06:11 GMT

Try below:

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(RANGESUM(BRO_ORC+AGE_ORC+GRO_ORC)=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))) <0,

ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100)),

SUM(IF(BRO_ORC=0 and AGE_ORC=0 and GRO_ORC=0 and ORC_COM>0,ALT(SUM(COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))))

Re: Expression Error.

Tue, 12 May 2015 07:13:45 GMT

Dear Angad,

It is already tested and failed.

Priyantha.

Re: Expression Error.

Tue, 12 May 2015 07:15:13 GMT

Quick response highly appreciated.

Re: Expression Error.

Tue, 12 May 2015 07:17:56 GMT

try this then:

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(RANGESUM(BRO_ORC,AGE_ORC,GRO_ORC)=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))) <0,

ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100)),

SUM(IF(BRO_ORC=0 and AGE_ORC=0 and GRO_ORC=0 and ORC_COM>0,ALT(SUM(COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))))

Re: Expression Error.

Tue, 12 May 2015 07:18:42 GMT

If the above does not work, then final try:

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))) <0,

ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100)),

SUM(IF(BRO_ORC=0 and AGE_ORC=0 and GRO_ORC=0 and ORC_COM>0,ALT(SUM(COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))))

Re: Expression Error.

Tue, 12 May 2015 07:25:37 GMT

Did not work.

Re: Expression Error.

Tue, 12 May 2015 07:31:46 GMT

Dear Angad,

Final one also failed..

Can you explain the error i have made in my 1st attempt?

Priyantha.

Re: Expression Error.

Tue, 12 May 2015 07:38:07 GMT

The error line is in bold below from your original exp:

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(RANGESUM(BRO_ORC+AGE_ORC+GRO_ORC=0) and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))) <0,

ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100)),

SUM(IF(BRO_ORC=0 and AGE_ORC=0 and GRO_ORC=0 and ORC_COM>0,ALT(SUM(COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))))

1) in RANGESUM you need comma separated values

2) the brackets of the IF should be before the =

The best thing will be to test the expression by breaking it into smaller peices.

Thanks,

Singh

Re: Expression Error.

Tue, 12 May 2015 07:42:39 GMT

Thkx.

Re: Expression Error.

Tue, 12 May 2015 07:49:37 GMT

Finlay i tried the below one,

But still it is not working.

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))) <0,

ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100)),

SUM(IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE)))).

Priyantha.

Re: Expression Error.

Tue, 12 May 2015 07:57:19 GMT

Can you please break it into smaller pieces like first investigate value of below:

ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE)))

one by one remove the lines to see where you get values, and then add them again.

Re: Expression Error.

Tue, 12 May 2015 07:58:36 GMT

OK..

Re: Expression Error.

Tue, 12 May 2015 08:08:43 GMT

By remaining line by line it was observed the bold and underlined parts makes the error.

If(ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100,0)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))) <0,

ROUND(RANGESUM(SUM(IF(CAT='BR',(COM_PRE*BR_COM_ORC)/100,0)),

SUM(IF(CAT='AG',(COM_PRE*AG_COM_ORC+SR*AG_RS_ORC+TC*AG_TC_ORC)/100)),

SUM(IF(CAT='GR',(COM_PRE*BR_COM_ORC)/100)),

//SUM(IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

-SUM(CRE))))

Please anyone can suggest something new instead of mine.

Priyantha.

Re: Expression Error.

Tue, 12 May 2015 08:13:08 GMT

Can you replace the bold line with below:

IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,SUM((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)),

Re: Expression Error.

Tue, 12 May 2015 09:24:22 GMT

Dear Angad,

Even though i replace the part, status of the expression shows error in expression.

Thanks.

Priyantha.

Re: Expression Error.

Tue, 12 May 2015 09:30:02 GMT

ohh sorry, you need to remove extra ) from right before comma.

IF(RANGESUM(SUM(BRO_ORC),SUM(AGE_ORC),SUM(GRO_ORC))=0 and ORC_COM>0,SUM((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0),

Re: Expression Error.

Tue, 12 May 2015 09:44:07 GMT

Dear Angad,

Also failed. anyway thanks for the great help.

Priyantha.

Re: Expression Error.

Wed, 13 May 2015 03:22:45 GMT

No Idea..........

Will try an other way

Thanks lot.

Priyantha.