How about something like the attached?
This is actually fairly complex, and I would be happy to go into detail on what enables this work over a screen sharing session, you are welcome to email me (found on my profile).
In brief, this uses nested table calculations, first to get the date of the next record with a LOOKUP to get a difference in days (partitioning on ID, addressing on Date), and second uses a WINDOW_SUM to aggregate a count of IDs that meet the day difference requirement, and divide by the number of IDs to get a percent (partitioning on Date, addressing on ID).
There are a number of other factors at play here, and I am happy to go in great detail over a screen sharing session.
next day.twbx 43.0 KB
Thanks so much for replying, Joe. When I try to apply your solution to my data, though, the calculations are just returning a bunch of nulls.
I did find a completely alternate way to get what I need, but I'd still like to understand your solution and how it works better. Are there some tutorials I should watch (I haven't done much with table calcs before)?
I know Tableau is working on making more resources in regards to Table Calculations, but I do not know if any they currently have cover all the techniques used in this workbook.
I am happy to have a screen sharing session with you to walk though what is going on. Please let me know if you have any concerns. Thank you!
If a screen share is to take place, I would love to join in!
I'd love a screen-sharing session with both of you, if you're willing. I'm pretty new at this, though - how will we set it up?
I was also interested in viewing how Joe approached this problem -- a join me session or google hangout will typically work if you can plan the timing out. I'm on Indiana time, and Joe's 3 hours earlier than I am, so that is the difficulty.
If I were you, I would reach out to Joe directly (his email is in his profile) to try and set something up. If it works out, I would love to be watch and learn more as well.
I reached out to Joe early this afternoon and within a matter of hours he was walking me through his thought process in building a solution for your type of problem in Tableau. He also recorded the entire thing!
There are some complex Tableau concepts discussed, so feel free and do not be afraid to ask questions. Reach out to either of us via email if you have comments on the video response idea as a whole.
I, personally, got a lot out of watching Joe work and asking for some clarifications along the way. Let this video serve as proof that all Zen Masters are human (and we make mistakes too).
Cheers and thanks to Joe and Ashley both!
I'm interested in getting your feedback on the video in particular, Ashley Flanagan - did the video help you, or did we miss the mark completely? Any honest feedback will be appreciated, and we of course want to know if you've resolved your User Retention problem -- and if so, what route did you take?
I apologize for not getting back to you all right away - I've been out of town for a few days. Joe, Matthew, thank you so much - the video was incredibly helpful.
Going through your steps with my data worked perfectly right up until nearly the last step - creating a combined ID & Date field and dragging it onto Path. Doing that produced the following totally bizarre graph:
Anyone have any idea what went wrong here?
In the Percent formula, instead of:
THEN 1 ELSE 0 END
THEN 1 END
That way you get null values for days that are zero, then you can adjust the Format->Pane for the Percent pill on the Rows shelf, setting Marks under Special Values to whatever you like. see 34:30 in the video for example.
Although there may be some other factor at play here, eg Show Missing Values, I would need an example packaged workbook that represents your situation, that recreates what you are experiencing to provide actual assistance.
Unfortunately, the actual data is confidential, and I'm really not sure what about it might be causing the issue.
I've got two other questions about the video, if that's all right.
- Around 28:39, when you first make the line chart, the y-axis goes from 0% to 4%, and the mark seems like it's averaging around 2%. As soon as you change it to a bar chart, the y-axis goes up to 100%, and it looks as if the average day is somewhere around 60%. Why does simply changing the mark type cause this?
- Why does the line chart only go crazy after you change the Date dimension from discrete to continuous? I think there's something I'm not understanding about the nature of discrete vs. continuous values here. (See below.) Come to think of it, what exactly would be wrong with just leaving the dates discrete?
(EDITED TO ADD: So after doing some research on my own, I'm guessing the answer to my initial question in #2 is something to do with data densification. I learned from this thread that turning on Stacked Marks is a way to turn on data densification, so I tried doing that instead of creating the combined field and putting it on Path, and I've got something that looks like a normal line chart with continuous dates now...except that the y-axis range is still really low, just like in question #1; it's giving me a retention rate of 2% on a date when the bar chart says I have 72%.)
I still haven't figured out what problem I was having with my data, so on a whim I tried Jim's method for this on the "Sample - Superstore Subset" sample data (using Customer ID & Order Date) and the "Sample - Superstore - English" sample data (using Customer Name & Order Date) - and I'm getting exactly the same problem I saw with my data.
Can anyone confirm whether they can make Jim's method work using the sample data? This might help isolate what I'm doing wrong.
I'm not sure exactly what you're referring to here when you say you tried Jim's method, but why not post your sample packaged workbook here? To post an attachment, in your reply choose "Use advanced editor" in the top right corner and then "Attach" will appear in the bottom right corner next to "@ Mention"
All right - this is a quick and dirty attempt to replicate the method from Jim's video posted upthread, using Sample-Superstore-English. Sheet 1 has the bar graph version; Sheet 2 is what happens when I try to turn it into a line graph by making the Date field continuous, dragging a combined Order Date & Customer Name field onto Path, and sorting the pill by Order Date with Aggregation: Minimum.
sample_retention.twbx 998.2 KB