3 Replies Latest reply on Feb 6, 2017 7:50 AM by Shinichiro Murakami

# Using LOD Calc to Find Min Order Date per Customer Each Year

Hello - I'm wondering if it's possible to use an LOD calc to find a customer's first order date, within each given year (in my case, school year). I'm looking at Teachers placing their first order for each school year, and trying to chart how many teachers "activated" on a particular day or month over time (i.e., a running count of a customer's first order).

This is the LOD that I've tried:

if [School Year] = 'SY16' then {fixed [Customer Name]:min([Order Date])} end

The LOD calc seems to still look within the entire 4 year data set to find that customer's first order date - see chart below. How do I stop it from looking outside of each "year"? I've found I can achieve this with custom SQL, but I'd like it to be more flexible - hoping with Tableau I'd be able to see Min Activation Date by Year by different "categories" - e.g., first order date for Product Type A, first order date for Product Type Z, etc, rather than having to create many different "activation types" by product category in my custom SQL.

Thanks!

Chantal

• ###### 1. Re: Using LOD Calc to Find Min Order Date per Customer Each Year

Hi Chantal,

Try below formula.

Current

if [School Year] = 'SY16' then {fixed [Customer Name]:min([Order Date])}

should be

{fixed [Customer Name] : min(if [School Year] = 'SY16' then [Order Date] end)}

Thanks,

Shin

3 of 3 people found this helpful
• ###### 2. Re: Using LOD Calc to Find Min Order Date per Customer Each Year

Thank you Shin, this works perfectly! In case anyone else runs into this problem, I also realized that LOD expressions can be fixed by multiple items. The calc below produces the same result as Shin's restructuring of my original calc:

{fixed [Customer],[School Year]:min([Order Date])}

Thanks to this post from Breaking BI for helping me realize that, and thanks again Shin!

• ###### 3. Re: Using LOD Calc to Find Min Order Date per Customer Each Year

Sounds good.

Just one more thing.  Could you mark my answer as correct to close the thread.

Thanks,

Shin