3

I have a list of documents that contains the same key but different dates,and I'm trying to bring all itens from key using data filter, im using Tableau Desktop.

For example, my table is:

Document Date Key
A 01/01/2021 X
B 01/02/2021 X
C 01/03/2021 X
D 01/04/2021 X
E 01/05/2021 X
F 01/06/2021 X
G 01/07/2021 Y
H 01/08/2021 Y

If I filter feb/2021, since my key X has the date 01/02/2021, results should be:

Document Date Key
A 01/01/2021 X
B 01/02/2021 X
C 01/03/2021 X
D 01/04/2021 X
E 01/05/2021 X
F 01/06/2021 X

Else if im filtering the date aug/2021, it should be:

Document Date Key
G 01/07/2021 Y
H 01/08/2021 Y

What I tried: I created a date parameter "Insert Date" to insert a single date and I created a calculated field "Select Date" using FIXED like the code bellow:

{ FIXED [Key] : MAX([Date] = [Insert Date])}

I got it done with a single day but i need using the entire month.

kdh90
  • 45
  • 4
  • So create a calculated formula that returns the keys with documents that meet the date criteria. Something like `IF document date = 01/02/2021, THEN key ELSE 0 END` That will give you all the keys that have documents with the date. You can apply your filter to that calculation so that you only get the data for the key you want. Note: you may need to change the `ELSE 0` to a different value if there's a chance that 0 is a valid key value. Also note, you can use a parameter for the date so it's not a static value in your formula – devlin carnate Sep 27 '21 at 20:25
  • Sorry @devlin carnate, i forgot to mention i got it done using the method above – kdh90 Sep 27 '21 at 20:28
  • So then use a calc formula to check if the date is within the desired month instead of an exact date... you can still use the logic I suggested. – devlin carnate Sep 27 '21 at 20:29

1 Answers1

2

It sounds like you want to first identify the set of keys that show up at least once within your selected date range, and then include all records for the identified keys, regardless of the date on each record.

If so, you don't want to filter record based on the dates, but you do want the user to specify a date (or date range). So instead of using a filter control for the date, use a parameter - either to allow the user to select a day within the month you want, or have two parameters to select the start and end of a range.

Then for the filter define an aggregate calculation or a set that determines whether a key occurs in the proposed time frame. A set named, say KEY_OF_INTERST based on the Key field, defined by a condition similar to the following should work

MAX([Date] >= [START DATE PARAM] and [Date] <= [END DATE PARAM])

That expression is True if at least one record falls within the specified date range, so the set will include exactly the Keys that have at least one document record in the time range.

Then just use the set to filter to the interesting keys

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49