Forum Discussion

MaryDay's avatar
MaryDay
Frequent Visitor
7 years ago
Solved

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 f...
  • Anonymous's avatar
    Anonymous
    7 years ago

    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
        __2VisitsExist

     

     

    Best

    Darek

MaryDay's avatar
MaryDay
Frequent Visitor
7 years ago

Anonymous  maybe you have some ideas? please 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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
    __2VisitsExist

 

 

Best

Darek

  • MaryDay's avatar
    MaryDay
    Frequent Visitor
    7 years ago

    Thank you very much for the answer. But the fact is that there can be more than two visits in 14 days and even several in one day, that is, the dates will be repeated.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      My code does not tell you that there were 2 visits within 14 days but that there were AT LEAST 2 different dates no more than 14 days apart on which the person visited. This is how I understood your description.

       

      If you need something different, then you could try to adjust the code. From what you've replied it looks like you don't want to check if there were at least 2 different days within a 14-day span but that there were at least 2 visits regardless of whether on the same day or not.

       

      Can you please confirm?

       

      Best

      Darek

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      -- calculated column without the use of context transition
[At Least 2 Visits Within 14 Days] =
var __custId = C[CustomerID]
var __visitDatesWithCounts =
ADDCOLUMNS(
    SUMMARIZE(
        FILTER (
            V,
            V[CustomerId] = __custId
        ),
        V[VisitDate]
    ),
    "CountOfSameDayVisits",
    	var __visitDate = V[VisitDate]
    	return
	    	COUNTROWS(
		        FILTER (
		            V,
		            V[CustomerId] = __custId
		            && V[VisitDate] = __visitDate
		        )    		
	    	)
)
var __2VisitsOnSameDayExist =
	MAXX(
		__visitDatesWithCounts,
		[CountOfSameDayVisits]
	) > 1
var __2VisitsOnDiffDaysExist =
    NOT ISEMPTY(
        FILTER(
            CROSSJOIN(
                SELECTCOLUMNS (
                    __visitDatesWithCounts,
                    "FirstVD", V[VisitDate]
                ),
                SELECTCOLUMNS(
                    __visitDates,
                    "SecondVD", V[VisitDate]
                )
            ),
            [SecondVD] - [FirstVD] < 14
            && [SecondVD] > [FirstVD]
        )
    )
return
	__2VisitsOnSameDayExist || __2VisitsOnDiffDaysExist

      Best

      Darek