Expression formula(with if condition)

[#1]

Dear All,

I'm looking for your kind help in following calculation

my table preview shown blow.

I want to calculate the BRO_ORC of 1% where BRO_COMM was calculated.As REF_NO,POL_NO, and CLA_CODE are equal it's difficult to build a formula in this connection. "2019-027" is a broker code where the relevant commission was calculated in the field BRO_COMM. "2022-001"is a Agent code where agent commission was calculated in the field of "AGE_COM" .  "7510" , "6297"  and many more ORC codes available where   BRO_ORC and AGE_ORC should be calculated accordingiy.

Your early attention would be much appreciated.

CRE_CODE	REF_NO	POL_NO	CLA_CODE	PREMUM	BRO_COMM	BRO_ORC	AGE_COM	GRO_COM	A	TOT_COM	DIFF	TOTAL
2019-027	BA0014D0028271	BA00141B0002417	MC	43584	5241		0	0	0	5241	0	0
7510	BA0014D0028271	BA00141B0002417	MC	43584	0		0	0	0	383	0	0

CRE_CODE	REF_NO	POL_NO	CLA_CODE	PREMUM	BRO_COMM	BRO_ORC	AGE_COM	GRO_COM	A	TOT_COM	DIFF	TOTAL
2019-027	BA0014D0028346	BA00121A001111	MC	64483	7885		0	0	0	7885	0	0
6297	BA0014D0028346	BA00121A001111	MC	64483	0		0	0	0	583	0	0

CRE_CODE	REF_NO	POL_NO	CLA_CODE	PREMUM	BRO_COMM	BRO_ORC	AGE_COM	GRO_COM	A	TOT_COM	DIFF	TOTAL
2022-001	BA0014D0028336	BA00141H0000281	MC	1593	0		139	0	0	139	0	0
6297	BA0014D0028336	BA00141H0000281	MC	1593	0		0	0	0	30	0	0
Total				1593	0		139	0	0	169	0	0

Regards,

Priyantha.

[#2]

Though I have not exactly understood your issue, but what you can do is use aggr(expression to calculate ORC's) function over REF_NO,POL_NO, and CLA_CODE fields.

[#3]

Dear Angad,

The Expression i have tried is below.

IF(CAT='BR',IF(ORC='ME',if('BRO_COMM'=0,if('AGE_COM'=0,if('GRO_COM'=0,(COM_PRE)/100,0)))))

But it is not working.

Regards,

Bandara.

[jyothish8807 · Accepted solution]

Hi Priyantha,

try:

Exp1:

alt(sum({<Cat={'BR'},ORC={'ME'},BRO_COMM={'0'},AGE_COM={'0'},GRO_COM={'0'}>}COM_PRE),0)

Exp2:

Exp1/100

Regards

KC

[#5]

Dear Kc,

Thanks for the early reply,

The formula you suggest was working, Calculation is being done against the broker codes and agent codes instead of calculating   against the ORC codes.

Anyway thanks again for the effort.

Regards,

Priyantha.

[jyothish8807]

Hi Priyantha,

Now wahat is not getting calculated.Can you please explain a bit about the issue?I just modified the expression you used.

Regards

KC

[#7]

Dear Kc

Please see the table i have posted carefully, Same POL_NO and REF_NO duplicated. Commission should be calculated to the Broker, Agent and Group where i labeled them as BRO_COMM,AGE_COM and GRO_COM. in addition to that over riding commission of 1% to be calculated to the marketing personnel where i labeled them as BRO_ORC). According to my database these are two records under the same REF_NO and POL_NO.

So I need to calculate the said over riding commission against the relevant ORC code. (7510,6297) see second row.   

Regards,

Priyantha.