topic Re: Nested Count in QlikView

Nested Count

dhanu_today — Sun, 16 Mar 2014 07:04:05 GMT

Hi Everyone,

Can any one help me as per the attached requirement.I have to develop the logic in front end level because the sample data whatever the fields we have is coming from various tables.

Thanks in advance,

Dhanu

Re: Nested Count

dhanu_today — Sun, 16 Mar 2014 07:21:33 GMT

Any one is there for hint .....

Thanks,

Dhanu

Re: Nested Count

MK_QSL — Sun, 16 Mar 2014 08:18:25 GMT

What is the COUNT? i.e what is the logic you used to count?

Re: Nested Count

dhanu_today — Sun, 16 Mar 2014 08:41:08 GMT

Thanks...For in the exp1 i have counted the value based on the No. of accounts.

For Exp2--the sum of balances and for exp3 is sum(balance)/Count(accounts)

Main logic i have to do is based on the exp3 values i have to create the bucket and their respective no. of accounts.

e.g. if exp3 is 5000 then we have to show the how many accounts are in that bucket range.

Refer the attached output.

Let me know in case of any information my side.

Thanks ,

Dhanu

Re: Nested Count

MK_QSL — Sun, 16 Mar 2014 09:05:56 GMT

Segemnt	Count(Accounts)	sum(Balance)	EXP2/EXP1	<= 0 and <2000	>=2000 AND <30000	>=30000 AND <100000	>= 100000
PRB	7	180000	25714.29	0	2	0	0
SB	6	354245	59040.83	0	0	2	0
CBC	9	183581	20397.89	0	1	0	0

Sorry, still have doubt.

Can you tell me how the bold and underlined value coming?

The logic is apply to expression 3? or I am missing something?

Re: Nested Count

dhanu_today — Sun, 16 Mar 2014 09:18:11 GMT

Thanks for your time...The Bold and underline values(2 in PRB) are the count of accounts which are having the balance 20000 and 28000(in the Data of excel) so they are in the bucket of >=20000 and <100000.

Same wise for SB and CBC also.

Its my mistake in the out put sheet i wrote >=2000 instead of >=20000.

Let me know for any clarifications.

Re: Nested Count

MK_QSL — Sun, 16 Mar 2014 09:27:39 GMT

You can use below expressions

COUNT({<Balance = {'>=0<20000'}>}Accounts)

COUNT({<Balance = {'>=20000<30000'}>}Accounts)

COUNT({<Balance = {'>=30000<100000'}>}Accounts)

COUNT({<Balance = {'>100000'}>}Accounts)

Let me know if these are not working...

Re: Nested Count

dhanu_today — Sun, 16 Mar 2014 09:32:44 GMT

Let me try the same and will get back....

Re: Nested Count

dhanu_today — Sun, 16 Mar 2014 10:38:36 GMT

Thanks ...But script is not filling my requirement....It is giving the count of accounts using the field balance and for bucket as well.

We have to consider the exp3 result as balance(like derived balance) and then we have to count the no. of accounts based on the exp3 result bucket(not on the basis of existing balance).

Let me know for any clarifications.

Thanks,

Dhanu

Re: Nested Count

MK_QSL — Sun, 16 Mar 2014 14:34:16 GMT

Ok... using the above mentioned criteria, let me know that the result you have shown in the excel file is accurate or the assumed one?

Re: Nested Count

dhanu_today — Sun, 16 Mar 2014 15:16:10 GMT

The result am showing in the excel file is accurate.In the excel i have shown the data as per the req and the same we have to show in our application as well.

Thanks,

Dhanu

Re: Nested Count

dhanu_today — Sun, 16 Mar 2014 16:37:57 GMT

Any one is there for to give hint for the above.

Thanks,

Dhanu

Re: Nested Count

MK_QSL — Sun, 16 Mar 2014 16:45:01 GMT

I can but unfortunately I still don't understand your logic.

If you can explain your logic in more briefly with excel file...

Re: Nested Count

dhanu_today — Sun, 16 Mar 2014 17:03:22 GMT

Please find the in detail logic as well.I hope it will clear your doubts.

Let me know if any clarifications required.

Thanks,

Dhanu

Re: Nested Count

dhanu_today — Sun, 16 Mar 2014 18:04:43 GMT

some one please look into the same.Its a little bit urgent requirement.

Thanks,

Dhanu

Re: Re: Nested Count

hectorgarcia — Mon, 17 Mar 2014 03:18:24 GMT

may be this will help i did at script level

Re: Re: Nested Count

qlikpahadi07 — Mon, 17 Mar 2014 05:40:10 GMT

Dear Dhanu,

I assume you have only 4 Buckets and gone through the 2nd excel attached by you.

Please find the attachment and let me know it works or not?

Cheers!!!!

Re: Nested Count

dhanu_today — Mon, 17 Mar 2014 05:59:03 GMT

Thanks Hector and Qlikpahdi.The script seems to be very nice and will check and close the thread or if in case of any concerns.

Thanks,

Dhanu

Re: Nested Count

qlikpahadi07 — Tue, 18 Mar 2014 04:10:16 GMT

Hi Dhanu,

was that helpful ?

Re: Nested Count

dhanu_today — Tue, 18 Mar 2014 15:57:32 GMT

Thanks qlikpahad...Its helpfull.

Dhanu