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

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

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.

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

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

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.