CODEWITHSUNDEEP
tableau-desktop
Thgiliwt
Thgiliwt

Reputation: 1

Tableau Calculation to dynamic rolling distinct count per month

I was asked to convert my previous Excel and PowerBI dashboards to Tableau and I encountered the problem where I need to show base on the [invoice date], the distinct count of [supplier code] in each month, together with the distinct count of [supplier code] over previous 13 month from the current month. I have made a brief example as below:

My dataset: enter image description here

This is all of the invoices for each supplier code over 5 year period (1/jan/2019 - 31/dec/2023), as you can see, each day could have multiple [supplier code].

My goal: (based on Year-Month level)

enter image description here

This will be an area chart, between the distinct count of [supplier code] for that month, and the distinct count of [supplier code] for a rolling 13 months, i.e.:

  1. there will be no value for the period from Jan 2019 to Jan 2020, as these periods don't have 13 months of data;
  2. the first 13-months distinct count point will be at Feb 2020, all the way to Dec 2023.

The current distinct count is easy, it goes with whatever the dimension I drag to the row/column, just something as 'COUNTD([Supplier Code])'. The pain is for the rolling 13 months, I have no idea how Tableau takes the filter into its calculation.

I would like to know how do I give Tableau the dynamical date range (i.e. 13months previous from each current month).

I also confused about the 'FIXED' method, I got something like:

{FIXED [Supplier Code],[Invoice Date]:
MIN(IF [Invoice Date] = {EXCLUDE [Invoice Date] : MIN([Invoice Date])}
THEN 1
ELSE 0
END
)}

when I dragged this to the table, it generates the new distinct count of [supplier code] of the current month, compare to all months before current month. well this is my best approach at the moment, as if I use another calculation of 'RUNNING_SUM(SUM())' on the above code, it gets the correct result for 'Feb 2020', as it is the first distinct point of the rolling 13 months. And then it just adds whatever the new distinct count of [supplier code] all the way to the last month of my dataset.

Looking forward to different approaches here~

Upvotes: 0

Views: 412

Answers (1)

MUFF.
MUFF.

Reputation: 791

You can create a calculated field to grab the last 13 months of your invoice date and base it off the TODAY() date function to make it dynamic.

Make a calculation and name it [Rolling Invoice Date]:

IF [Invoice Date] >= DATEADD('month', -13, TODAY()) AND [Invoice Date] <= TODAY() THEN [Invoice Date] END

This checks if the invoice date is inside a date range of 13 months prior to today or up to today.

To help clarify: FIXED calculations pin or "fix" the level of detail for the calculation to whatever level of the field you put before the colon in that calculation. Read more on it here.

Upvotes: 0

Related Questions