topic Re: Min Date after every 11months. in QlikView

Min Date after every 11months.

marthand — Tue, 27 Jan 2015 20:03:34 GMT

I need to get min date after every 11months for distinct cust code for given ID.

ID is unique

Starting from 1st date then after 11months again 1st date for each cust code.

Data:

Date	Cust Code	ID
25-JAN-15 03.30.56AM	1	100
25-NOV-14 03.34.02 AM	1	101
25-JAN-10 04.57.27 AM	1	50
25-JUN-10 04.57.39 AM	1	52
25-JUN-10 04.57.56 AM	1	53
25-APR-11 04.58.13 AM	1	54
25-APR-12 04.58.32 AM	1	55

Re: Min Date after every 11months.

jonasheisterkam — Tue, 27 Jan 2015 20:28:37 GMT

Is the 11 month period starting for each custCode at its first timestamp

-at periot

-at first periot and then without pause every 11month

-same start for all custCodes.

If you starting not at the first at a month what is 11 month after 31.Jan.

Re: Min Date after every 11months.

marthand — Tue, 27 Jan 2015 21:03:03 GMT

Starting from min date. I need after every 11months what is min date for cust.

Imagine for the first row(highlighted) after 11month(2/15/2011) next start date is 3/4/2011 from that date. and again 11months.

After 11months, it need to pick next min date and go on from there.

example:

date	11months
3/15/2010	-
3/15/2010	2/15/2011
7/14/2010	6/14/2011
3/4/2011	2/4/2012
3/20/2012	2/20/2013
4/11/2012	3/11/2013
2/22/2013	1/22/2014
3/6/2013	2/6/2014
6/18/2013	5/18/2014
1/14/2014	12/14/2014
3/31/2014	2/28/2015
5/29/2014	4/29/2015
7/25/2014	6/25/2015

Re: Min Date after every 11months.

jonasheisterkam — Tue, 27 Jan 2015 21:34:30 GMT

You must order by date and all fields you will user as category, use a load with peek.

load date,

if(AddMonths(if(isnull(peek('Period')),0,peek('Period')),11) <= date, date, peek('Period')) as Period

Resident ...

(if(isnull(peek('Period')),0,peek('Period')) in this if you check if you starting the 11 month chaining. 0 starts new chain peek('Period') keeps last chain.

AddMonth(...,11)<date checks if date is greater 11 month from last row => get same Period or if it is graeter 11 month get date as new period start date.

Re: Min Date after every 11months.

MarcoWedel — Tue, 27 Jan 2015 21:36:08 GMT

Hi,

one solution could be:

LOAD Distinct Alt(If(date>=AddMonths(Peek(date11months),11),date,Peek(date11months)),date) as date11months
FROM [http://community.qlik.com/thread/149775] (html, codepage is 1252, embedded labels, table is @2);

hope this helps

regards

Marco

Re: Min Date after every 11months.

jonasheisterkam — Tue, 27 Jan 2015 21:45:42 GMT

I hope this files helps