
1. Re: LOD Calculations that compare a date inside the subquery to a date outside the subquery
Joshua Milligan Feb 15, 2017 2:47 PM (in response to Graeme Frost)2 of 2 people found this helpfulGraeme,
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!
Joshua

2. Re: LOD Calculations that compare a date inside the subquery to a date outside the subquery
Peter Galimutti Feb 15, 2017 3:51 PM (in response to Graeme Frost)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#comment24607.
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
Prior Value
sum(if
[Date1]<DATEADD([Period],[Last N Periods],TODAY())
and [Date1]>=dateadd([Period],([Last N Periods]*2),TODAY())
//and [Date]>(TODAY()7)#
then [Value] end)
Current Value
sum(if
[Date1]<TODAY()
and [Date1]>=dateadd([Period],[Last N Periods],Today())
//example 
then [Value] end)
I hope this is close to what you are looking for. Let me know if you have any questions or clarifications.
Thanks,
Peter

3. Re: LOD Calculations that compare a date inside the subquery to a date outside the subquery
Graeme Frost Feb 15, 2017 3:59 PM (in response to Peter Galimutti)Thanks Peter
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
Graeme

4. Re: LOD Calculations that compare a date inside the subquery to a date outside the subquery
Graeme Frost Feb 15, 2017 4:05 PM (in response to Joshua Milligan)Thanks Joshua
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
Graeme

5. Re: LOD Calculations that compare a date inside the subquery to a date outside the subquery
Peter Galimutti Feb 15, 2017 4:36 PM (in response to Graeme Frost)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?
https://public.tableau.com/views/LODproblem/Dashboard1?:embed=y&:display_count=yes

6. Re: LOD Calculations that compare a date inside the subquery to a date outside the subquery
Graeme Frost Feb 15, 2017 5:58 PM (in response to Peter Galimutti)Hi Peter
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
Cheers
Graeme

7. Re: LOD Calculations that compare a date inside the subquery to a date outside the subquery
Yuriy Fal Mar 27, 2017 5:34 PM (in response to Graeme Frost)Hi Graeme,
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:
Re: How to count distinct users on a running period
There is yet another way to solve the particular case
of Moving CountD in a 3Month window at the grain of a month.
The idea behind it is to precalculate 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 (3Month) date ranges
into their quarters' first dates.
Please find the attached with the latter.
Hope it could be of some help.
Yours,
Yuri
PS The same logic could be applied
to calculate Moving CountD for cases of
12Month, 4Quarter and 7Day.
Besides, the latter (7Day) had been solved first:

LOD problem_YF.twbx 401.3 KB
