topic Re: how to find second minimum value for each unique field in QlikView

how to find second minimum value for each unique field

Thu, 18 Sep 2014 07:44:54 GMT

Hi All,

I am new to qlikview and am having trouble in finding the second min value for each customer. My table is as follows:

Customer	Service Date
1	2014/09/11
2	2014/09/12
3	2014/09/12
1	2014/09/08
3	2014/09/14
4	2014/0912
5	2014/09/20
2	2014/09/15
2	2014/09/17
3	2014/09/20

I am able to get the minimum service date for each customer with the following expression:

=Date(Min([Session Started], [Consumer ID]), 'MM/DD/YYYY')

Now i need to find the second minimum service date for each customer. Please let me know how do we achieve this.

Thanks!!!!

Re: how to find second minimum value for each unique field

MarcoWedel — Thu, 18 Sep 2014 07:47:13 GMT

min(field, 2)

Re: how to find second minimum value for each unique field

Thu, 18 Sep 2014 07:49:50 GMT

Hi Marco,

This does not give the desired output. I want the second minimum of the field 'Service Date' for each customer

Re: how to find second minimum value for each unique field

MWiciok — Thu, 18 Sep 2014 08:02:58 GMT

Hi,

=aggr(min([Service date], 2), customer)

Re: how to find second minimum value for each unique field

jagan — Thu, 18 Sep 2014 08:16:25 GMT

Hi Ramya,

Try like this

Straight Table:

Dimension : Customer

Expression: min([Service date], 2)

Hope this helps you.

Regards,

jagan.

Re: how to find second minimum value for each unique field

jagan — Thu, 18 Sep 2014 08:19:55 GMT

Script:

TableName;

LOAD Customer,

Date(Date#([Service Date], 'YYYY/MM/DD')) AS ServiceDate

FROM

[http://community.qlik.com/thread/133850]

(html, codepage is 1252, embedded labels, table is @1);

Straight Table:

Dimension : Customer

Expression: min(ServiceDate, 2)

Hope this helps you.

Regards,

Jagan.

Re: how to find second minimum value for each unique field

MK_QSL — Thu, 18 Sep 2014 08:30:32 GMT

Check enclosed file...

Re: how to find second minimum value for each unique field

Thu, 18 Sep 2014 10:44:26 GMT

Thanks for the help guys!!!