Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day everyone.
I came across the discussion below and I have a very similar request but the data is slightly different.
I need to count consecutive months per client. so my data would look like the left two columns and the result must be like the right hand column. I am extremely new to Qliksense and any help would be highly appreciated.
This code should do it:
data:
load *,
Num(Date#(YM,'MMM-YY')) as [NUM YM];
load * Inline
[
ID,YM
1,Jan-16
1,Feb-16
1,Mar-16
1,Apr-16
1,May-16
2,Jan-16
2,Feb-16
2,Mar-16
2,May-16
3,Jan-16
3,Feb-16
3,Mar-16
];
NoConcatenate
OrderedData:
load *,
if
(
ID=peek(ID),
peek([NUM YM]),
// if
// (
// [NUM YM]=Num(AddMonths(peek([NUM YM]),1)),
// 1,
// 0
// ),
[NUM YM]
) as [Flag],
if
(
ID=peek(ID),
if
(
[NUM YM]=Num(AddMonths(peek([NUM YM]),1)),
1,
0
),
1
) as [Flag2]
Resident data
order by ID,[NUM YM];
drop table data;
It gives me the following,
As you have on your spreadsheet
You can simplify in table like.
Count(TOTAL <ID> YM)