Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!

Calculate lost/new customers trend

Highlighted
7 - Meteor

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
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @agoyal28 

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


Cheers,

Highlighted
7 - Meteor

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.

Highlighted
7 - Meteor

Hi - any ideas, please?

Highlighted
12 - 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?

Highlighted
7 - Meteor

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

Highlighted
12 - 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