1 Reply Latest reply on Feb 5, 2019 7:56 AM by patrick.byrne.0

How to ignore Date Filter on a LOD whilst using date as a dimension AND filter?

After reading this post: How do I get LOD to ignore date filter? , I am wondering if it is possible to do the following:

1. Count the number of users who purchased a product in 2018 <- Let's call this "2018_Users"
2. Create a field with the calculation: "# of users who purchased a product in 2019" / 2018_Users
3. Show this on a weekly basis

I use a LOD calculation to find the 2018 users using this:

{EXCLUDE [Order Date]: COUNT(

{FIXED [End User], [Account Name]: MAX(

IF [Order Date] >= DATEADD('day', -365, DATETRUNC('quarter', TODAY())) AND

[Order Date] < DATETRUNC('year', TODAY()) AND [Is Sample?] = FALSE

THEN 1

ELSE NULL END

)

}

)}

This is fine and gives me what I want as shown below, but I want to show this on a weekly basis in 2019. So basically I want to keep the 2018_Users static regardless of the date filter I put on it.

Measures
20182019
Count of 2019 Users0100
Count of 2018 Users10001000

If I add in a date filter for just 2019, it looks like this:

Measures
Week 1
Week 2
Count of 2019 Users3070
Count of 2018 Users5050

From what I understand, because EXCLUDE happens after dimension filters, it is performing the LOD calc on a filtered dataset of just 2019 users. Thus, the 50 shown is the count of 2018 users who reordered in 2019. How can I make sure that I get that 1000 shows up regardless of if I filter on date? I have to use EXCLUDE to ensure the LOD doesn't get influenced by Order Date. Is there a workaround for that?

Please don't ask for sample data as this is a conceptual question, so there's no need to use my data to answer it.

EDIT: I realized I could just hide the columns that aren't 2019, so this question is not needed.

• 1. Re: How to ignore Date Filter on a LOD whilst using date as a dimension AND filter?

Hello Joseph,

I am happy to hear that a solution was found! Thank you for updating the question!

Cheers,

Byrne, Patrick

Community Manager

Tableau Software