Forum Discussion
Duplicate values on time span
Hello! I have a selection of customer names and dates of visits. In both columns the data can be repeated. I need to identify those customers who visited cafe more than one time in 14 days. I can't figure out how to do this. Please, help. Example:
11.06.2012 Mark +
15.06.2012 John -
17.06.2012 Mark +
17.06.2012 Sam +
29.06.2012 Sam +
05.06.2012 Piter -
05.06.2012 John -
I think this is what you need:
Find the names of the customers that have 2 different visit dates not more than 14 days apart.
Let's say your table that stores visits is V, you've got CustomerID and VisitDate in there. Then you also have a dimension table with your customers C where you store unique CustomerID's. C joins to T in a 1:many fashion on CustomerID. Then you could add a column [2 Visits Within 14 Days] to C:
[2 Visits Within 14 Days] = -- calculated column without the use of context transition
var __custId = C[CustomerID]
var __visitDates =
SUMMARIZE(
FILTER (
V,
V[CustomerId] = __custId
),
V[VisitDate]
)
var __2VisitsExist =
NOT ISEMPTY(
FILTER(
CROSSJOIN(
SELECTCOLUMNS (
__visitDates,
"FirstVD", V[VisitDate]
),
SELECTCOLUMNS(
__visitDates,
"SecondVD", V[VisitDate]
)
),
[SecondVD] - [FirstVD] < 14
&& [SecondVD] > [FirstVD]
)
)
return
__2VisitsExistBest
Darek
7 Replies
It should be a cycle that checks every 14 days from the first date. That is, from 01.06 to 14.06, then from 02.06 to 15.06, and so on. I need a list of customers who visited cafe more than once in 14 days at any time.
Anonymous maybe you have some ideas? please
I think this is what you need:
Find the names of the customers that have 2 different visit dates not more than 14 days apart.
Let's say your table that stores visits is V, you've got CustomerID and VisitDate in there. Then you also have a dimension table with your customers C where you store unique CustomerID's. C joins to T in a 1:many fashion on CustomerID. Then you could add a column [2 Visits Within 14 Days] to C:
[2 Visits Within 14 Days] = -- calculated column without the use of context transition
var __custId = C[CustomerID]
var __visitDates =
SUMMARIZE(
FILTER (
V,
V[CustomerId] = __custId
),
V[VisitDate]
)
var __2VisitsExist =
NOT ISEMPTY(
FILTER(
CROSSJOIN(
SELECTCOLUMNS (
__visitDates,
"FirstVD", V[VisitDate]
),
SELECTCOLUMNS(
__visitDates,
"SecondVD", V[VisitDate]
)
),
[SecondVD] - [FirstVD] < 14
&& [SecondVD] > [FirstVD]
)
)
return
__2VisitsExistBest
Darek
