Am I able to group dates together based on number of days between dates?

[#1]

I'm doing some work on customer calls to calculate repeat calls and need to apply some logic where if they call within 5 days of previous call, the calls are considered as related and just need 1 record and calc the number of repeated called within the period.

See example below

Customer ID	Call Date	Count
123	01-05-2014	1
123	02-05-2014	1
123	06-05-2014	1
123	08-05-2014	1
123	14-05-2014	1
124	01-05-2014	1
124	03-05-2014	1
124	04-05-2014	1
124	10-05-2014	1

So we are checking by customer where there has been a gap of 5 days or more in calls and grouping accordingly.The table above would become:-

Customer ID	Call Date	Call Count	Repeat Calls Exist?
123	01-05-2014	4	Y
123	14-05-2014	1	N
124	01-05-2014	3	Y
124	10-05-2014	1	N

Note that the Call Date in the 2nd table is now just the min date from the grouped data. Am I able to do this in Qlikview?

[marcus_sommer]

You could do it within a by Customer ID and Call Date sorted (resident) load and using peek() or previous() to look in the previous data-rows and check these values against each other, example:

t1:

Load

     *,

     if(rowno() = 1, 1,

     if([Customer ID] = peek("Customer ID", -1) and

          [Call Date] - peek("Call Date", -1) <= 5, peek("Call Count", -1) + 1, 1) as [Call Count]

Resident abc;

Maybe you need a little bit more of check-logic but generally this will work.

- Marcus

[MarcoWedel · Accepted solution]

Hi,

tabCalls:
LOAD [Customer ID],
    [Call Date] as [Call Dates]
FROM [http://community.qlik.com/thread/129902]
(html, codepage is 1252, embedded labels, table is @1);

tabCallCount:
LOAD [Customer ID],
    Alt(If([Customer ID] = Previous([Customer ID]) and [Call Dates] <= Previous([Call Dates])+5, Peek(CallID), Peek(CallID)+1), 1) as CallID,
    [Call Dates]
Resident tabCalls
Order by [Customer ID], [Call Dates];

Left Join (tabCallCount)

LOAD *, If([Call Count]>1, 'Y', 'N') as [Repeat Calls Exist];
LOAD [Customer ID],
    CallID,
    Count(CallID) as [Call Count],
    Date(Min([Call Dates])) as [Call Date]
Resident tabCallCount
Group By [Customer ID], CallID;

hope this helps

regards

Marco

[MarcoWedel]

if your input table is already sorted like in this case, you can also try with:

tabCalls:
LOAD [Customer ID],
    [Call Date] as [Call Dates],
    Alt(If([Customer ID] = Previous([Customer ID]) and [Call Date] <= Previous([Call Date])+5, Peek(CallID), Peek(CallID)+1), 1) as CallID
FROM [http://community.qlik.com/thread/129902]
(html, codepage is 1252, embedded labels, table is @1);

tabCallCount:
LOAD *, If([Call Count]>1, 'Y', 'N') as [Repeat Calls Exist];
LOAD [Customer ID],
    Count(CallID) as [Call Count],
    Date(Min([Call Dates])) as [Call Date]
Resident tabCalls
Group By [Customer ID], CallID;

hope this helps

regards

Marco