Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
I hope this files helps
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.
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 |
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.
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
I hope this files helps