Code for Count Calculation

[rohitians]

Dear Users,

I have a scenario where in I want to count the number of sanction accounts for the month,

but if the product is ADDL_LOAN I need to check for Linked Lan if any Lan is found I will club the count with same basically whichever lan is found under  Linked lan of  ADDL_LOAN should not be counted ...

SZ_APPLICATION_NO	DT_DISB_DATE	SZ_PRODUCT_CODE	Linked Lan	Sanction Count
AHMHL16000201	31-05-2016	LAPSORP	-	1
AHMHL16000202	31-05-2016	HLSELLERBT	-	0
AHMHL16000203	31-05-2016	ADDL_LOAN	AHMHL16000202	1
AHMHL16000196	31-05-2016	HLRDYPURBD	-	0
AHMHL16000206	31-05-2016	ADDL_LOAN	AHMHL16000196	1
AHMHL16000144	23-05-2016	LAPSORP	-	1
AHMHL16000152	31-05-2016	HLRDYPURBD	-	0
AHMHL16000204	31-05-2016	ADDL_LOAN	AHMHL16000152	1

Thanks and Reagrds,

Rohit Yadav

[migueldelval]

Hi Rohit,

If all "linked Lan" has a 0 value, You can use this expreession.

=count(distinct{<[Linked Lan]={"*0*"}>}SZ_APPLICATION_NO)

Right now is ok in your example,

Regards

Miguel del Valle

[Kushal_Chawda · Accepted solution]

see if below expression helps

=Count({<SZ_APPLICATION_NO=e({<SZ_APPLICATION_NO =p({<SZ_PRODUCT_CODE={'ADDL_LOAN'}>}[Linked Lan])>}SZ_APPLICATION_NO)>}SZ_APPLICATION_NO)

[rohitians]

Dear Miguel,

Your solution is partially correct the count is not coming right where there is no ADDL_LOAN or linked lan,

also Count for  Lan no  should only be avoided if it comes  in Linked Lan for ADDL_Loan

Request you to help,

Thanks,

Rohit

[antoniotiman]

Try

=count({<SZ_PRODUCT_CODE={'ADDL_LOAN'}>} If(Len(Trim([Linked Lan])) = 0 or [Linked Lan]='-',SZ_PRODUCT_CODE))

Regards,

Antonio