1.Create a parameter [Date Parameter] for your target date. Set it up to allow the date to be typed in.
2.Create a calculated field like "date check":
if [Sale Date] = [Date Parameter] or
[Revenue Date] = [Date Parameter] or
[Refund Date] = [Date Parameter] or
[Survey Date] = [Date Parameter] or
[Regisration Date] = [Date Parameter]
then 'Y' else 'N' END
3. Drag this calculated field to filters and filter to Y.
This can be a tough problem, as that data structure is horrible (from "doing work on the data" perspective)...and while it does have some capability, Tableau is a viz tool first, with a bit of data manipulation/re-shaping thrown in! I would look to get your data in a different format, if possible.
However there is a way, and probably only if you have these 5 fields (else it becomes too laborious), to force the data into the "right" shape.
First I Unioned your data to itself 5 times!
Then using the Table Name I created a single date instance in a formula
CASE [Table Name]
WHEN 'Sheet1' THEN [Sale Date]
WHEN 'Sheet11' THEN [Revenue Date]
WHEN 'Sheet12' THEN [Refund Date]
WHEN 'Sheet13' THEN [Survey Date]
WHEN 'Sheet14' THEN [Registration Date]
and we also have to create a new version of each measure...
[Sales To Use]
IIF([Table Name] = 'Sheet1',[Sale],NULL)
[Revenue To Use]
IIF([Table Name] = 'Sheet11',[Revenue],NULL)
and so on...
Now the data is 5 times it's usual size, but if you extract it into a TDE you'll be fine with 5-10 million rows, and barley notice (especially if you Optimize the extract after you've written the calculations for Combined Date and Measure To Use...Tableau should materialise those values, so it don't have to calculate them each time)
Another option would be to use CustomSQL to reshape the data, or if you only want to see all the activity on a particular day, you could create a parameter of type = date, and set to any. You could then write an IF statement, which included a check on each of your 5 date fields.
Hope that helps.
Multiple Date Problem.twbx 32.3 KB
Please see the twbx file attached.
For the data source, I have added two more lines to make more dates. What you need to do is:
(1) Build a parameter: Date Search. Added members through "added from filed" and added all the five date: sales date, revenue date, registration date, refund date, survey date.
(2) Make a calculated field: Search
CONTAINS(STR([Sale Date]),STR([date search]))
CONTAINS(STR([Refund Date]),STR([date search]))
CONTAINS(STR([Registration Date]),STR([date search]))
CONTAINS(STR([Survey Date]),STR([date search]))
CONTAINS(STR([Revenue Date]),STR([date search]))
(3) Put the calculated field to filter, select 'True"
(4) Right click parameter, select "show parameter control".
When you select a date, all records will be shown for a certain day you select with the parameter.
Data Search.twbx 13.5 KB