This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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!
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.
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
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.