topic Re: Count with multiple Conditions in QlikView

Count with multiple Conditions

Thu, 05 Dec 2013 16:14:07 GMT

Hi,

I want to calculate how many applications are sanctioned. The criteria is: Sanctioned = Sanctioned-not Booked+Booking Started+Lender Decision for the following data. Can I just use <Case expression> to get the above or would have to must use Appl_ID

APPL_ID	<CASE expression>
19681349	ENTERED
19737483	DECLINED
11089833	Booking Started
11829485	BOOKED
13145469	DECLINED
10510490	DECLINED
15079449	BOOKED
16357070	BOOKED
17715414	Lender Decision
19097166	BOOKED
12671357	DECLINED
12973150	DECLINED
13417909	DECLINED
15294770	BOOKED
17432881	SYSTEM DECISION
13579447	BOOKED
19212301	Sanctioned-Not Booked
10369751	REFERRAL EVENT
15752122	BOOKED
14255114	DECLINED
10240186	DECLINED
19214097	Cancelled
15874564	BOOKED
12260756	DECLINED
11978952	BOOKED
15505655	Cancelled
11368921	BOOKED
11087854	Lender Decision
13137807	BOOKED
16159271	Cancelled
16510501	BOOKED
10393592	BOOKED
10007616	BOOKED
11916336	BOOKED
18191506	BOOKED
14763573	BOOKED
10256936	REFERRAL EVENT
18988955	BOOKED
13848021	DECLINED
17872078	BOOKED
16139673	BOOKED
16067788	Sanctioned-Not Booked

Re: Count with multiple Conditions

kouroshkarimi — Thu, 05 Dec 2013 16:23:01 GMT

assuming that <Case expression> is a field name, then try

count({$<[<Case expression>]={'Sanctioned-not Booked','Booking Started','Lender Decision'}>} APPL_ID)

Re: Count with multiple Conditions

Thu, 05 Dec 2013 16:30:32 GMT

Yes case expression is a field name. Basically I want to show in pie chart % of applications sanctioned (Sanctioned-not booked+Booking started+Lender decision), declined and Booked. Appl_ID is the primary key.

Re: Count with multiple Conditions

MK_QSL — Thu, 05 Dec 2013 16:40:14 GMT

Is this what you are looking for?

Re: Count with multiple Conditions

Thu, 05 Dec 2013 16:44:59 GMT

Yes,

Re: Count with multiple Conditions

MK_QSL — Thu, 05 Dec 2013 16:54:37 GMT

if you requirement fulfilled, please close this discussion by making correct answer.

Thanks...

Re: Count with multiple Conditions

Thu, 05 Dec 2013 16:56:57 GMT

I am still looking for the solution because the above formula did not work. Could you please tell how can I draw pie chart as discussed above.

Re: Count with multiple Conditions

Thu, 05 Dec 2013 17:00:01 GMT

can u please paste the formula becuase unable to download file

Re: Count with multiple Conditions

MK_QSL — Thu, 05 Dec 2013 17:03:27 GMT

1) Select PIE chart

2) Dimension = CASE

3) Expression 1 = COUNT(APPL_ID)

Tick Value on Data Points

4) Expression 2 = if(Count(DISTINCT CASE)=1, CASE)

Tick Value on Data Points

5) Presentation Tab

Untick Show Legend....

Re: Count with multiple Conditions

Mon, 09 Dec 2013 09:42:09 GMT

Thanks Manish but I need to get total sanctioned applications and the logic should be as below:

Sanctioned = Sanctioned-Not Booked + Booking started + Ledner Decision.

How can I use this logic in the formula to count Sanctioned Not booked, Booking started and Lender decision as one legend

Re: Count with multiple Conditions

MK_QSL — Mon, 09 Dec 2013 09:59:18 GMT

use below in script..

TEMP:

Load * Inline

[

APPL_ID, CASE

10007616, BOOKED

10240186, DECLINED

10256936, REFERRAL EVENT

10369751, REFERRAL EVENT

10393592, BOOKED

10510490, DECLINED

11087854, Lender Decision

11089833, Booking Started

11368921, BOOKED

11829485, BOOKED

11916336, BOOKED

11978952, BOOKED

12260756, DECLINED

12671357, DECLINED

12973150, DECLINED

13137807, BOOKED

13145469, DECLINED

13417909, DECLINED

13579447, BOOKED

13848021, DECLINED

14255114, DECLINED

14763573, BOOKED

15079449, BOOKED

15294770, BOOKED

15505655, Cancelled

15752122, BOOKED

15874564, BOOKED

16067788, Sanctioned-Not Booked

16139673, BOOKED

16159271, Cancelled

16357070, BOOKED

16510501, BOOKED

17432881, SYSTEM DECISION

17715414, Lender Decision

17872078, BOOKED

18191506, BOOKED

18988955, BOOKED

19097166, BOOKED

19212301, Sanctioned-Not Booked

19214097, Cancelled

19681349, ENTERED

19737483, DECLINED

];

MAIN:

Load *, IF(CASE = 'Sanctioned-Not Booked' or CASE = 'Booking Started' or CASE = 'Lender Decision', 'Sanctioned',CASE) as SCND Resident TEMP;

Drop Table TEMP;

----------------------------------------

1) Select PIE chart

2) Dimension = SCND

3) Expression 1 = COUNT(APPL_ID)

Tick Value on Data Points

4) Expression 2 = if(Count(DISTINCT SNCD)=1, SCND)

Tick Value on Data Points

5) Presentation Tab

Untick Show Legend....

Re: Count with multiple Conditions

Tue, 10 Dec 2013 14:39:51 GMT

Manish, thanks for that but I have loads of data. Is it possible to get the above with an expression instead of code.

Re: Count with multiple Conditions

MK_QSL — Tue, 10 Dec 2013 14:57:10 GMT

you can use the same as Calculated dimension..

IF(CASE = 'Sanctioned-Not Booked' or CASE = 'Booking Started' or CASE = 'Lender Decision', 'Sanctioned',CASE)

Re: Count with multiple Conditions

Tue, 10 Dec 2013 15:14:56 GMT

didnot work