I mean: "[...] I want to make a chart [...]"
Hello Srinidhi Narayan!
Thanks for giving time to answer my question and help me!
Are you accounting all the client that purchased in the last 24 months prior to each of months on the x-axis? Can you attach your workbook so I can understand better your data?
I am not sure I understand what you mean by "all clients that purchased in the last 24 months prior to each of months on the x-axis".
Not in a position to upload a workbook right now. What i drew was a simple viz based on Sample Superstore as described. i.e. it is a plot of count distinct of customers (on rows) and continuous monthi of order date on columns. I apologize, it seems to me that I might have oversimplified your original question.
Do you want a count of all customers that have purchased this month, but who have also made a purchase sometime over the last 24 months?
1 of 1 people found this helpful
From the sounds of it you're looking for a monthly moving count of the number of customers who have purchased in the last 24 months.
The first question is what is the grain of your Tableau data source?
If it's at the level of customer & month of purchase then a view with Month as the dimension and a calculated measure WINDOW_SUM(SUM([Number of Records]),-23,0) with a Compute using on the Month will get you the desired results.
If the data is at a finer grain like Tableau's sample Superstore that is the at the level of individual order items then it gets a lot more complicated to do in Tableau because we can't use a WINDOW_SUM() as is, we need to be effectively doing a moving count distinct and that is not a native Tableau function. There are a variety of techniques for doing this in posts that are linked from Utilization, Queues, Census, Throughput for Blocks, Floors, Units, Rooms, Offices, etc. - What do you do?, I can't recommend any particular technique without knowing more about your data source, volumes of data, need for filtering, etc.
I would like to add some to Jonathan's answer.
Here attached is a (version 10.0) sample workbook
with two sets of LOD (Level-Of-Detail) calculations --
for both 12-Month and 4-Quarter Moving (Rolling)
Distinct Count of [Customer Name] field values.
I've made the same set of calcs previously,
but that were for 7-day (1-Week) Moving CountD.
I hope you'd be able to extend the calculation logic
from 12-Months to 24-Months -- just add months 13-24
and extend the Moving CountD calculation accordingly.
PS Performance of FIXED LODs depends on many factors --
mainly the data size (# Rows as well as # Customers)
and the DBMS of choice (extracts would perform well --
up to a point where they're not :-).
Moving_CountD_v10_YF.twbx 1.2 MB