3 Replies Latest reply on Aug 29, 2018 3:14 PM by James D

    See customer orders that were cancelled by cohort within a window of 10 days

    James D

      Hello. Hopefully someone can help me figure this out.

       

      I'm trying to create a calculated field measure to give the % of sales orders that were later cancelled within 10 days (regardless of what month the cancellation takes place) divided by all sales within the month the sale took place in.

       

      For the example data below, I would expect this calc to return 33% for the percentage of sales in January which were cancelled within 10 days, because customers A and C had a sale in January, and A cancelled in 7 days, and C cancelled in 9 days. Customers B, D, E, F had sales in January, but either did not cancel yet, or cancelled after 10 days from the sale.

       

      My data is like follows:

       

      customerevent_typeevent_date
      ASale1/1/18
      BSale1/2/18
      CSale1/5/18
      DSale1/20/18
      ACancel1/8/18
      CCancel1/14/18
      ESale1/25/18
      FSale1/30/18
      GSale2/1/18
      BCancel2/5/18
      HSale2/10/18
        • 1. Re: See customer orders that were cancelled by cohort within a window of 10 days
          swaroop.gantela

          James,

           

          This may not work for your full dataset, but maybe it could give some ideas.

           

          I arranged a table by Customer then by ascending date

          which allowed me to use this to flag a cancellation for counting:

          IF ATTR( [Event Type] ) = "Cancel" AND

          DATEDIFF('day', LOOKUP ( ATTR ( [Event Date] ) , -1) , ATTR([Event Date]) )<=10

          THEN 1 ELSE 0 END

           

          Then the percent cancelled would be:

          WINDOW_SUM([CountCancel])

          /

          WINDOW_SUM(SUM(IF [Event Type]="Sale" THEN 1 ELSE 0 END))

           

          I made one more field EventDate (Months) using

          Custom Dates

           

          and then used that to reset the Table Calculations for every month

          (please see table calculation settings below).

           

          I suspect that your true dataset will be more complicated than your sample

          with customers having multiple purchases per month. In that case, there will

          need to be some kind of Transaction ID that ties the Sale to the Cancel.

           

          There are other methods that could be used (self-join on Customer), but

          they may not be practical for a large dataset.

           

          Please see the workbook v10.3 attached in the Forum Thread.

           

          280206canc.png

          1 of 1 people found this helpful
          • 2. Re: See customer orders that were cancelled by cohort within a window of 10 days
            Deepak Rai

            Hi James,

            Here It is, You need to create this calc and apply to Filter and Set True to get this.

             

            Thanks

            Deepak

            If it Helps, Pl mark it Helpful and CORRECT to close Thread

            1 of 1 people found this helpful
            • 3. Re: See customer orders that were cancelled by cohort within a window of 10 days
              James D

              Thanks, all. I was able to find a solution on my own before viewing your solutions:

               

              1) create "sale date" calc: {fixed [customer]:min(if [event_type]="sale" then [event_date] else null end)}

              2) create "cancel date" calc: {fixed [customer]:min(if [event_type]="cancel" then [event_date] else null end)}

              3) create "days to cancel" calc: [cancel date]-[sale date]

              4) create "% of customers who cancel within 10 days" calc: COUNTD(IIF([event_type]="sale" and [.days to cancel]<10,[customer],null)) / COUNTD(IIF([event_type]="sale",[customer],null))

               

              This approach does not require any filters, or table view with any sorting. Just returns the value by itself, with no other dimensions or measures in the view.