Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Table:
LOAD *,
MonthName(Date) as MonthYear
INLINE [
Date, CustomerID
11/5/2014,3
11/25/2014,2
11/7/2014,1
11/30/2014,4
12/15/2014, 1
12/13/2014, 2
12/1/2014, 2
12/12/2014, 4
1/11/2015, 1
1/15/2015, 3
1/1/2015, 4
2/14/2015, 5
2/17/2015, 3
2/16/2015, 1
2/1/2015, 2
3/17/2015, 5
3/21/2015, 1
3/14/2015, 3
3/11/2015, 4
4/5/2015,2
4/15/2015,5
4/1/2015,1
4/28/2015,3
];
WANT:
April 2015 - 2
March 2015- 1
Feb 2015- 2
Jan 2015 - 0
Dec 2015 - 0
Nov 2015 - 0
I am ok with either solution. Front end or back end, whichever gets me the correct solution.
Try the attached qvf
What are those ID which belong to April, just so we understand what got included and how
maybe you can find some help here?
April - 2 (ID's are 3 and 1)
March - 1 (ID - 1)
Feb - 2 ( ID's 4 and 1(
Hope this helps explains it better. THANKS!
Something like this would work?
Yes, exactly that. Would you be able to send me the script. I am a Qlik Sense user, not able to open up a qlikview file. Thanks!
Sure thing:
Table:
LOAD *,
MonthName(Date) as MonthYear
INLINE [
Date, CustomerID
11/5/2014,3
11/25/2014,2
11/7/2014,1
11/30/2014,4
12/15/2014, 1
12/13/2014, 2
12/1/2014, 2
12/12/2014, 4
1/11/2015, 1
1/15/2015, 3
1/1/2015, 4
2/14/2015, 5
2/17/2015, 3
2/16/2015, 1
2/1/2015, 2
3/17/2015, 5
3/21/2015, 1
3/14/2015, 3
3/11/2015, 4
4/5/2015,2
4/15/2015,5
4/1/2015,1
4/28/2015,3
];
FinalTable:
LOAD *,
MonthName(AddMonths(MonthYear, 1)) as New_MonthYear;
LOAD *,
If(AddMonths(Previous(Previous(MonthYear)), 2) = MonthYear and AddMonths(Previous(MonthYear), 1) = MonthYear and
CustomerID = Previous(Previous(CustomerID)) and CustomerID = Previous(CustomerID), 1, 0) as Flag;
LOAD DISTINCT
MonthYear,
CustomerID
Resident Table
Order By CustomerID, MonthYear;
DROP Table Table;
This works with the original data, but breaks at this point.
The results should still be the same
May - 3 (ID's 3, 5, 1)
April - 2 (ID's are 3 and 1)
March - 1 (ID - 1)
Feb - 2 ( ID's 4 and 1(
Table:
LOAD *,
MonthName(Date) as MonthYear
INLINE [
Date, CustomerID
11/5/2014,3
11/25/2014,2
11/7/2014,1
11/30/2014,4
12/15/2014, 1
12/13/2014, 2
12/1/2014, 2
12/12/2014, 4
1/11/2015, 1
1/15/2015, 3
1/1/2015, 4
2/14/2015, 5
2/17/2015, 3
2/16/2015, 1
2/1/2015, 2
3/17/2015, 5
3/21/2015, 1
3/14/2015, 3
3/4/2015, 5
3/7/2015,5
3/11/2015, 4
4/5/2015,2
4/15/2015,5
4/1/2015,1
4/28/2015,3
];
FinalTable:
LOAD *,
MonthName(AddMonths(MonthYear, 1)) as New_MonthYear;
LOAD *,
If(AddMonths(Previous(Previous(MonthYear)), 2) = MonthYear and AddMonths(Previous(MonthYear), 1) = MonthYear and
CustomerID = Previous(Previous(CustomerID)) and CustomerID = Previous(CustomerID), 1, 0) as Flag;
LOAD DISTINCT
MonthYear,
CustomerID
Resident Table
Order By CustomerID, MonthYear;
DROP Table Table;
EXIT SCRIPT;
Try the attached qvf
Worked! Thanks Sunny!