cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

## 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

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

So Looking solely at Jerry

 Original Change Reason April 1 1 May 1 0 June 1 0 July -1 Aug 1 ? +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