topic Re: Code for Count Calculation in QlikView

Code for Count Calculation

rohitians — Mon, 04 Jul 2016 08:49:19 GMT

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

Re: Code for Count Calculation

migueldelval — Mon, 04 Jul 2016 09:09:05 GMT

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

Re: Code for Count Calculation

Kushal_Chawda — Mon, 04 Jul 2016 09:27:08 GMT

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)

Re: Code for Count Calculation

rohitians — Mon, 04 Jul 2016 09:31:11 GMT

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

Re: Code for Count Calculation

antoniotiman — Mon, 04 Jul 2016 09:34:58 GMT

Try

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

Regards,

Antonio