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

Alteryx designer Discussions

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

Comparing occurrences month on month

Highlighted
Meteor

Hi all,

 

I am looking to compare certain occurrences for customers by month.

 

(Attached spreadsheet for explanation) : I am trying to compare data such as the following, what I want to be able to show is that in August one customer was lost, in October one was gained and in June one was gained, looking at the previous 6 months for the historic.

 

Just a bit lost trying to work out how to compare the historic lack of occurrence to a first occurrence in a column as well as historic occurrences to a first lack of occurrence in Alteryx!

 

Many Thanks!

 

Oli

Aurora

hi @obridgeman 

 

I'm going to need some help interpreting your data.  Specifically what do you mean by looking at the previous 6 months for the historic.  For the 6 month period preceding August, we see that the number of customers starts at 2 in April.  It's 3 in May, June, July and drops back to 2 in August.   When you say that one customer is lost in August how exactly are you arriving at that number.  It's one fewer than July, but the same as April.

 

Dan

Asteroid

Hi there!

 

Made some assumptions, which you will need to validate as okay for your context. For none, gained, hold, lost:

 

1. The first recorded month is considered a "Gain" if Sum Value >0 i.e. you gained by whatever total tally is at end of first recorded month

2. The first recorded month is considered a "None" if  Sum Value =0 i.e. you gained by whatever total tally is at end of first recorded month

3. You are tallying chronologically by Year, Month and across Year without resetting to zero i.e. if you had 5 customers in Dec 2018 and 4 customers in Jan 2019 --> 1 customer lost

4. You also want a trigger (Fix Me) to call out situation that merits a closer look

 

Argument logic like so:

 

IF [RecordID] =1 AND [Sum_Value] >0 THEN "Gained "+ToString([Sum_Value])
ELSEIF [RecordID] =1 AND [Sum_Value] =0 THEN "None"
ELSEIF [Period] !=[Row-1:Period] AND [Sum_Value] =[Row-1:Sum_Value] THEN "Hold"
ELSEIF [Period] !=[Row-1:Period] AND [Sum_Value] >[Row-1:Sum_Value] THEN "Gained "+ToString([Sum_Value]-[Row-1:Sum_Value])
ELSEIF [Period] !=[Row-1:Period] AND [Sum_Value] <[Row-1:Sum_Value] THEN "Lost "+ToString([Row-1:Sum_Value]-[Sum_Value])
ELSE "FixMe"
ENDIF

 

Please see attachment; hope it helps.

Meteor

Hi Dan,

 

by losing a customer I mean that one specific customer dropped off (Laura). I was looking to see if possible to monitor that, where you can have a +1 or a -1 per month where an unique customer either begins or ends being a customer looking back over the past 6 months.

 

I do have the previous 6 months data for all.

 

Thanks,

 

Oli

Aurora

hi @obridgeman 

 

So Looking solely at Jerry

 

 OriginalChangeReason
April11 
May10 
June10 
July -1 
Aug1?+1 from July, but 0 from Apr-May 
Sept ?-1 from Aug, but 0 from  Jul 

 

what are the missing change values and why?

 

Dan

Labels