You may want to look at this workbook as an example (shameless plug):
In the example the Churn is the same nature
as the metric you're trying to build.
Hope it could help a bit.
Hi Yury - thanks so much for the reply! These explanations are closer than anything I have seen so far and I think I get what is being communicated but am not sure it covers it all. At its lowest level I am looking for multiple months/time periods being displayed for this customer movement (or churn) - I did see a 3 month window in one example but I am looking for up to at least a year for summary of this information. Do you think this is possible?
In my workbook there is a Parameter to choose a Period,
and it could be Year, Quarter or Month.
Following the workbook logic, Churn is defined as
Customers present in the previous Period,
but absent in the current one.
What would be your Churn logic that differs?
Hi Yuri - the Churn definition matches but I guess it is my understanding of how time runs over the axis. The screen shot shown here shows multiple months and I am looking to expand the # of months being reviewed. So, like the below chart but showing at least 12 months. Hope that makes sense?
Denis, i beg your pardon for missing your data structure completely.
You have all Counts as the Aggregates (by Week #).
My example would work with a different data shape --
they're all user transactions for at least 3 periods (minimum 3 months).
The raw data is at least at the granularity of [Customer ID] & [Period].
Since there's no [Customer ID] in your dataset, my calc logic --
based on COUNTD([Customer ID]) -- couldn't be applied.
Sorry about moving in a wrong direction.
No need to apologize as you have been a tremendous help so far! However, although you state I do not have a Customer ID field there is a "Seller ID" field and a "Seller Nm" or (Seller Name) field which should act in an identical fashion to the Customer ID field, as I understand it. Do you still feel this can't be resolved?
For the requirement of data to exist at the level of [Customer ID] & [Period] I would think this can be created in my db as a concatenated field. Or am I misunderstanding that?
Lastly, as far as Weeks vs. Months aren't they all just time periods? Ultimately, I would like to aggregate at both levels (Week and Month) but I believe starting with Week with provide the best analysis. This goes back to the original question of whether or not multiple time periods (for example, at least 26 weeks or 6 months) can be shown on a visualization with each independent months data of "churn" or accounts removed noted independently?
Could you please explain a bit about your data?
What do the Dimensions and Measures mean?
At least the ones below -- the [Removed?] Dim
and all the Measure Names in Columns, please:
Please find the attached w/Sheet 7, too.
See below for brief explanation for Measures and Dims (I hope I am explaining them all):
- count (AS OF DATE): counts the number of days in a week the seller is present. Max of 7, Min of 0.
- Seller Count (Present): counts if a seller was present in the current week. If # of days the seller is present in a week is 4 or more then the seller is present. If the # of days in a week is 3 or less the seller is not present.
- Prior Week Seller count?: counts if a seller was present (4 days or more) in the week just preceding. This is a "trick" since the weeks are all aligned in numerical order. I realize this would not work (I think) in a more structured db.
- Removed count: Simply counts or notes a "1" if the seller was present for the prior week and then not present in the current week. This is a formula (in excel) that shows ("Seller Count (Present)" less "Prior Week Seller count?")
- Removed Sellers: this is what I am really trying to achieve and what I want shown on the viz. - the # of sellers removed (comparing this week vs. the prior week).
I think some of the above is duplicative - just trying to get to the # removed.
Hope this makes sense.