After reading this post: How do I get LOD to ignore date filter? , I am wondering if it is possible to do the following:
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 | 2018 | 2019 |
---|---|---|
Count of 2019 Users | 0 | 100 |
Count of 2018 Users | 1000 | 1000 |
If I add in a date filter for just 2019, it looks like this:
Measures | Week 1 | Week 2 |
---|---|---|
Count of 2019 Users | 30 | 70 |
Count of 2018 Users | 50 | 50 |
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.
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
Become a Viz Whiz on the Forums! Support the Community and master Tableau.