10 Replies Latest reply on Dec 19, 2016 4:18 PM by Justin Lovern

# LOD to determine the Total Distinct Count up through a given date

I cannot provide a packaged workbook as the information cannot be shared, but I can attempt to describe the problem.

Ultimately, I need to calculate the penetration rate of individual products. This is done by dividing the total number of members  in that product by the total number of members active members up to the row date:

 Category Product Distinct count of Person# Count of Members Year of Contract_Date Penetration CNS INAU x y 2014 % x y 2015 % x y 2016 % INUA x y 2014 % x y 2015 % x y 2016 %

 Category Product Distinct count of Person# Count of Members Year of Contract_Date Penetration CNS INAU x y 2014 % x y 2015 % x y 2016 % INUA x y 2014 % x y 2015 % x y 2016 %

I am currently using {countd(Person#)} to get the total number of members, but this of course returns the all time total of all years. I just need the total up until the row date. For example, the distinct count of members who have opened an account (contract_date) up through 12/31/2014 would be the number in the 'y' in the first row.

• ###### 1. Re: LOD to determine the Total Distinct Count up through a given date

Justin, welcome to the forums! Hey Bill Lyons you want to set this new forum guy straight?

Justin, our collective knowledge will most definitely find an answer to your question; but just a wee bit of help and humility from you might be required. Do what Bill Lyons suggests.

Cheers,

--Shawn

• ###### 2. Re: LOD to determine the Total Distinct Count up through a given date

Thanks for the reference, Shawn.

First, welcome to the Forums. I hope this doesn't offend, but clearly some of your data can be shared, because you included it in your post. Please read Packaged workbooks and flows: when, why, how for details on how you can post a packaged workbook without violating confidential data.

Without seeing your exact problem in a workbook, I'm just guessing here. It sounds to me like you need to add your filter to the context to force Tableau to apply the filter to your FIXED LOD.

• ###### 3. Re: LOD to determine the Total Distinct Count up through a given date

Hi Bill and Shawn,

Thank you for getting back to me.

It didn't occur to me to replicate it using superstore so thank you for that tip in the link you provided. I have attached a packaged workbook using superstore and it is very similar to what I'm ultimately trying to get to. I tried adding order date to the filter here, but seem to have the same problem. It's tricky because it's similar to a running sum, but I just need the sum of the account from the first occurrence (regardless if in view or not) up to the row-level date.

With regard to some of my data being able to be shared I'm not sure what you are implying - I removed the type of data I couldn't share after I exported crosstab to Excel. I guess I thought it was obvious that the type of data I shared was shareable, but it seems you are implying that I was being dishonest in some way and I truly wasn't.

I also did spend some time in the forums trying to find the answer that way, but was unsuccessful - I really appreciate whatever help you guys are able to provide.

OH - I forgot to include in the post the I am using 10.1

Thank you

• ###### 4. Re: LOD to determine the Total Distinct Count up through a given date

I did not intend to accuse you of dishonesty. I'm sorry if it came across that way. My point is that a workbook can be stripped of the confidential information to just provide enough information to solve the particular problem.

Thanks for including the packaged workbook. In my attached version, compare Sheet 2 with your Sheet 1. In that, you will see that I added the filter to the "context," which changes the order of operations for FIXED LODs (LODs default to FIXED if the type - FIXED, INCLUDE, EXCLUDE - is omitted). In Sheet 2, if you change the filter years, the count of customers changes, but in Sheet 1, it does not change.

The order of operations diagram helps understand what is happening here:

See also http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#order_of_operations.html and Evolution of the Order of Operations Diagram for a more detailed discussion.

To add a filter to the context:

Does that help?

• ###### 5. Re: LOD to determine the Total Distinct Count up through a given date

No worries there. Thank you for the clarification

Either I'm blind or it appears your attachment didn't stick..?

• ###### 6. Re: LOD to determine the Total Distinct Count up through a given date

You aren't blind; I'm just forgetful. Sorry! Try refreshing it, it should show up now.

• ###### 7. Re: LOD to determine the Total Distinct Count up through a given date

Oh interesting...I was not aware of that context trick. I see what it did, but we aren't quite there yet. For each YEAR, there should be a different Count of Customers. That Count of Customers column should count the total number of customers up until AND including that year...Kinda tricky.

• ###### 8. Re: LOD to determine the Total Distinct Count up through a given date

So I think the context filter solution works IF I only have one year selected

• ###### 9. Re: LOD to determine the Total Distinct Count up through a given date

That should be possible by creating a calculated field to identify the qualifying dates, and filter on that, or by using a formula on the "Condition" tab of the edit filter dialog box.

Are you wanting the user to specify the year, or how will that value be derived?

• ###### 10. Re: LOD to determine the Total Distinct Count up through a given date

The year to be counted up to should be the year in the 'Year of Order Date' field. The goal is to see how the penetration rate is changing over time so that's why I have 3 years exposed in this example.

If you look at sheet 2 (or sheet 1) at the first 3 rows - that first 'count of customers' should be the total number of people who have made a purchase since the beginning of time up until and including the 'Year of Order Date' (in this case, 2015); the second row should be counting up to 2014 (I don't know why I didn't sort by year..)