-
1. Re: Filtering multiple columns
Jim DehnerJun 13, 2018 6:40 AM (in response to Paul Whitaker)
Good morning
see the attached
first create this lookup filter and place it on the filter shelf - it forces the table calc to be determined out of the order of operation
then this filter pon teh filter shelf and set to True
it will return this
Jim
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
-
test1 (2)_v10.2.twbx 19.8 KB
-
-
2. Re: Filtering multiple columns
Jim Van Sistine Jun 13, 2018 6:41 AM (in response to Paul Whitaker)Hi Paul,
You're correct. A LOD calculation is the key to getting started here.
I used this to find the last posted date for each Site in the data.
{fixed [Site]: max([Date])}
With that information, we can find the status of each site at the most current date.
MIN(IF [Date]=[LastDatebySite]
then [Status]
ELSE 'ZZZZ'
END)
And also, the status the month before.
MIN(IF
DATEDIFF('month',[Date],[LastDatebySite])=1
THEN [Status]
ELSE 'ZZZZ'
END)
By comparing those statuses, we can detect which sites have changed.
I'm attaching my workbook so you can see all of the calculations. Hope this helps!
- Jim
-
SiteStatusChanges.twbx 17.1 KB
-
-
3. Re: Filtering multiple columns
Zhouyi ZhangJun 13, 2018 6:42 AM (in response to Paul Whitaker)
-
test1_v10.2.twbx 18.6 KB
-
-
4. Re: Filtering multiple columns
Paul Whitaker Jun 13, 2018 6:50 AM (in response to Jim Dehner)Thanks Jim, i'll try this and the LOD ways below to see which works best for me!
-
5. Re: Filtering multiple columns
Jim DehnerJun 13, 2018 6:51 AM (in response to Paul Whitaker)
sure - there is usually more than one way to do things in Tableau
Jim
-
6. Re: Filtering multiple columns
Paul Whitaker Jun 14, 2018 6:53 AM (in response to Jim Van Sistine)Hi Jim VS,
This only seems to work due to there being 3 months where2 were 'on', one 'off' for site 7.
The aggregation then works as there are more of one state than the other.
When I reduce it to 2 months it stops working as all status changed flags go to 'true' (I think its comparing ON to ZZZZ and ZZZZ to OFF)
Its given me some food for thought though and i'll keep trying to tweak this method.
Regards
Paul W
-
7. Re: Filtering multiple columns
Jim Van Sistine Jun 14, 2018 7:22 AM (in response to Paul Whitaker)Paul,
Can you provide a mockup/picture of the viz you want to create, or what the question you want to answer is?
My solution will tell you if a site's most current status is different from where it was a month ago, which is how I understood the question. If you are trying to show each month in the viz, you're right the formulas I've built will break down.
You could try making those Current/Previous formulas LODs as well (set them to Fixed by Site).
{FIXED Site: MIN(IF [Date]=[LastDatebySite]
then [Status]
ELSE 'ZZZZ'
END)}
By doing that, I was able to create this view:
- Jim
-
SiteStatusChanges.twbx 19.4 KB
-
-
8. Re: Filtering multiple columns
Paul Whitaker Jun 14, 2018 8:40 AM (in response to Jim Van Sistine)Thanks again Jim,
I'll have a play with that!