community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Calculate lost/new customers trend

Meteoroid

I have data with columns as customer ID, product, week, month, sales (attached). I want to calculate the lost/new/growing/declining weekly customer trend and show it in power BI. (Attached is the output snapshot for reference). Here is the definition of definition of the metrics

  • NEW – Volume in recent 13 weeks; no volume prior 13 weeks
  • LOST – No volume in recent 13 weeks; volume in prior 13 weeks
  • GROWING – Volume in both recent & prior 13 weeks; most recent 13 weeks > prior 13 weeks
  • DECLINING – Volume in both recent & prior 13 weeks; most recent 13 weeks < prior 13 weeks
Alteryx Certified Partner
Alteryx Certified Partner

Hi @agoyal28 

When you mean recent, does it mean from today's date?


Cheers,

Meteoroid

Hi Thableaus,

 

By recent I mean the last week in the data. E.g in this dataset, week_num 1 is the latest week (02/15/2019) and 13 weeks means week_nu, 1-13.

Meteoroid

Hi - any ideas, please?

Quasar

@agoyal28 One question: Your description mentions looking at this on a rolling 13 week window for each customer. However, your example input shows the information aggregated at the month level (assuming I'm reading the chart correctly). These two things wouldn't add up. Can you clarify?

Meteoroid

@neilgallen - you can consider it weeks instead of months in the chart. So effectively we want to show the last 52 weeks trend.

Quasar

so there are probably much more elegant solutions to this, but I think this works. The concern is the eventual size of the data because of all the combinations. I haven't checked the customer labels, but you'll get the idea.

 

 

 

Labels