How can i create expression with if command?

[#1]

Hi,

Please help me to create the following expression in my project, even expression shows the status OK, it does not woks.

SUM(IF('BRO_COMM'=0 AND 'AGE_COM'=0 AND 'GRO_COM'=0,(COM_PRE*ME_COM+SR*ME_RS+TC*ME_TC)/100))

regard,

Priyantha.

[jsingh71]

Try this:

(IF('BRO_COMM'=0 AND 'AGE_COM'=0 AND 'GRO_COM'=0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100))

[jagan]

Hi,

Try this

=SUM({<BRO_COMM={'0'}, AGE_COM={'0'}, GRO_COM={'0'}>} ((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC)))/100

Hope this helps you.

Regards,

Jagan.

[#4]

Dear Priyantaha,

Try this...

if('BRO_COMM'=0 AND 'AGE_COM'=0 AND 'GRO_COM'=0,sum((COM_PRE*ME_COM+SR*ME_RS+TC*ME_TC))/100)

Thanks & Regards

Prince Anand

[#5]

Dear Jagan,

Thaks for the reply.

the formula you sent is worked, but ÏF" condition dose not works. it means, even above one of three expressions have values grater than "0" , formula result shows value (in this case result should be zero)

Regards,

Priyantha.

[jyothish8807]

Hi Priyanth,

Try using ALT function in the above expression suggested by Jagan.

=Alt(SUM({<BRO_COMM={'0'}, AGE_COM={'0'}, GRO_COM={'0'}>} ((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC)))/100,0)

Regards

KC

[#7]

Try below:

SUM(IF('BRO_COMM'='0' AND 'AGE_COM'='0' AND 'GRO_COM'='0',(((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100),0))

I assume the 'BRO_COMM', 'AGE_COM' are not fields but dynamic values coming from a variable.

Thanks,

Singh

[#8]

Dear Kc,

Thaks for the reply.

the formula you sent is worked, but ÏF" condition dose not works. it means, even above one of three expressions have values grater than "0" , formula result shows value (in this case result should be zero)

Regards,

Priyantha.

[jagan]

Hi,

Can you attach some sample data and explain with an example and expected output?

Regards,

Jagan.

[#10]

BCOD	CRE_CODE	REF_NO	PREMUM	BRO_COMM	BRO_ORC	AGE_COM	AGE_ORC	GRO_COM	GRO_ORC	ME_COM	TOT_COM	DIFF
BA00	2019-027	BA0014D0028271	43584	5241	383	0	0	0	0	0	5241	0
		BA0014D0028276	110605	13479	994	0	0	0	0	0	13479	0
		BA0014D0028346	64483	7885	583	0	0	0	0	0	7885	0
		Total	218672	26604	1959	0	0	0	0	0	26604	0
	C0001084	BA0014D0028368	48859	0	0	4261	915	0	0	0	4261	0
		Total	48859	0	0	4261	915	0	0	0	4261	0
	C0001731	BA0014D0028226	24974	0	0	2217	471	0	0	0	2217	0
		BA0014D0028309	21560	0	0	2000	416	0	0	0	2000	0
		BA0014D0028425	7040	0	0	1000	146	0	0	0	648	0
		BA0014D0028464	15932	0	0	2219	336	0	0	0	2134	0
		BA0014D0028465	6765	0	0	953	141	0	0	0	923	0
		BA0014D0028480	25625	0	0	3606	536	0	0	0	2325	0
		Total	101896	0	0	11996	2047	0	0	0	10247	0
	C0004862	BA0014D0028209	45500	0	392	0	847	5005	392	0	5005	0
		Total	45500	0	392	0	847	5005	392	0	5005	0
	C0004866	BA0014D0028249	13660	0	122	0	258	1503	122	0	1503	0
		BA0014D0028283	17953	0	157	0	337	1975	157	0	1975	0
		BA0014D0028328	6001	0	56	0	116	660	56	0	660	0

Dear Jagan,

Thanks for the quick reply.

See the above table. i want to get the output in the column 'ME_COM'. If   'BRO_COMM' , 'AGE_COM' and 'GRO_COM' columns available zero value then only the 'ME_COM' calculated('PREMIUM'*5%). in other word, if some value available in one of above three columns(grater than zero) the 'ME_COM' result should be zero.

Regards,

Priyantha