2 of 2 people found this helpful
Your inner IF statement is calculated at a row level, regardless of whether it's embedded in an aggregate (which it is - COUNTD) or an LOD (which it is - FIXED). But no matter what it is embedded in, it will always be calculated at a row level and at a row level the Order Date and Reporting Date are the same month (even if different days). That means that when you do the distinct count of customers at the level of Reporting Date, you're just getting the distinct count of customers for the month.
What you want to do is actually fairly complex. You might search the forums for distinct counts within a moving window (in this case a 3 month window). There are approaches that might work depending on your exact data.
Another option, again depending on your data, might be to join the set of data to itself so that for every month, you have a row for every customer for the month, the month - 1 and month -2 (if you are really ambitious, you could even dedup the data so that the row count itself would give you the distinct count). If you have a high volume of data, that might not be feasible. It's also going to mess up your granularity for most everything else (but you could work around it, or just use this special data set in a blend with your primary data set being the one you already have).
Hope that helps!
This sounds similar to a requirement of mine and I created an idea for simplifying 'Change over prior period'. https://community.tableau.com/ideas/7122?et=watches.email.idea_comment#comment-24607.
However we created manually formulas to overcome this. I also created a mockup of it on Tableau public https://public.tableau.com/views/CurrentvsChangeoverPreviousPeriod/Dashboard1?:embed=y&:display_count=yes
Basically you want to see the change of current selection vs prior three months, right? I had to use Parameters for Period and Last N Periods instead of date filters
[Date1]<DATEADD([Period],-[Last N Periods],TODAY())
and [Date1]>=dateadd([Period],-([Last N Periods]*2),TODAY())
then [Value] end)
and [Date1]>=dateadd([Period],-[Last N Periods],Today())
then [Value] end)
I hope this is close to what you are looking for. Let me know if you have any questions or clarifications.
Unfortunately what I want is slightly different, I want to be able to draw a graph showing the customers ordering in the previous three months for each month this year. So a parameter wont help, sorry!
Thanks for your effort though
Yes I guess my fallback is to do a view in SQL that creates a new extract - but I am trying to avoid that.
Thanks again for your input
No worries Graeme. Just be clear I mocked up some data based on your and created a graph ( in excel though for now). Is that what you are looking for?
Not quite! I could do that with a running total in Tableau, sorry its slightly more complex.
I want to know how many customers have placed at least 1 order in the previous 3 months. Adding them up will double count any customer that has ordered more than once in the period.
Thanks for your continued efforts
Joshua is right, one could approach
the Moving CountD problem in many ways.
The first one -- mentioned by Joshua -- is by using
a range join with the date dimension (a calendar)
at a grain of a view (a month in your case).
This approach would become easier
with the help of Join Calculations
(introduced in Tableau 10.2).
The second approach is by using
(Nested) Table Calculations.
Please refer to this thread to find out how:
There is yet another way to solve the particular case
of Moving CountD in a 3-Month window at the grain of a month.
The idea behind it is to pre-calculate three aggregates
using overlapping date partitions -- each one starting from
the 1st, 2nd and 3rd month of a quarter, then refer to one of them
when calculating COUNTD() for each (rolling) month.
All this requires just 4 calcs (including FIXED LOD).
The perceived simplicity of this approach
comes from the fact of having used the
DATETRUNC('quarter', ...) calculation,
which does all the magic of squeezing
the respective (3-Month) date ranges
into their quarters' first dates.
Please find the attached with the latter.
Hope it could be of some help.
PS The same logic could be applied
to calculate Moving CountD for cases of
12-Month, 4-Quarter and 7-Day.
Besides, the latter (7-Day) had been solved first:
LOD problem_YF.twbx 401.3 KB