topic Having conditional count on columns in QlikView

Having conditional count on columns

Fri, 24 Jun 2011 08:09:25 GMT

Hi Experts,

I have a scenario as below.

There are orders in the system which will be at a STAGE for 'n' number of days.And for each stage there will be 'M' days given as SLA.

Now I would like to have a column which gives me count of orders which have crossed the SLA.

ie, against each stage, I want the count of orders which has n>M.

Can some body pleeeease help me.

Many thanks in advance.

Lavanya

Having conditional count on columns

Fri, 24 Jun 2011 08:27:15 GMT

Hi Lavanya,

here a first guess for your chart-expression:

count( if(STAGE > SLA, order))

Regards, Roland

Having conditional count on columns

Fri, 24 Jun 2011 09:08:45 GMT

Thanks very much Roland.

Is it count(if(n>M, order)) ?

Because, STAGE is a stage at which Order is at.

I have tried it like this,

Dimension as STAGE_CODE and

Expression as count(if(n>M, order)) but it dint fetch me any result.

Thanks for your help once again.

Having conditional count on columns

kaushiknsolanki — Fri, 24 Jun 2011 09:36:16 GMT

Hi,

Try this.

Create a straight table.

Add dimension as Stage.

Add expression as count({<N = {">M"}>}Order)

Regards,

Kaushik Solanki

Re: Having conditional count on columns

Fri, 24 Jun 2011 09:36:34 GMT

Hi again,

this should work when 'n' and 'M' are fields belonging to the same table as 'STAGE_CODE' and 'order'. Or they are in a table with a link to the "order-table".

I am quite sure you (we) are not far away from the solution, but to find the last missing part it would be very helpfull if you post a little exam app. This can explain more then any description.

Having conditional count on columns

Fri, 24 Jun 2011 11:00:58 GMT

Thanks Roland.

You are right. I got it as below.

count(if(NUMBEROFDAYS >=SLA, PAG_ID)).

Thanks Koushik, I am there with Roland's suggestion.

Now since I have got it, is it possible for me into include some range in 'IF' condition.

like count( if ( n>m and n<p), pag_id) ?

I know I am just missing with small bit of logic..

Many thanks for your time and help.

Regards

Lavanya

Having conditional count on columns

kaushiknsolanki — Fri, 24 Jun 2011 11:05:21 GMT

Hi,

Yes you can do that.

If you want to achieve with set analysis. you will use below code.

count({<N = {">M<P"}>}Order)

Regards,

Kaushik Solanki

Re: Having conditional count on columns

Fri, 24 Jun 2011 12:16:03 GMT

Hi Lavanya,

glad to help you.

To answer your second quest: Yes you can, but take care of the brackets:

count( if ( n>m and n<p), pag_id)

---> should be: count( if ( n>m and n<p, pag_id))

This means that the if() delivers only those pag_ids to the count() which passed its filter.

Having conditional count on columns

Fri, 24 Jun 2011 14:26:24 GMT

Many thanks for your help Roland

Regards