6 Replies Latest reply on Jan 4, 2019 11:18 AM by Thomas Roth

# Calculated Field: Using the filtered Date as a parameter

Dear Tableau :-),

can you help me with the following problem:

Example for Base table:

IDRefDatePercent
12019-1-3100
22019-1-345
32019-1-344
12019-1-250
22019-1-243
32019-1-222
12019-1-1100
22019-1-122
32019-1-133

The column "RefDate" I use as a filter for the data.

Challenge:

When the User has selected the filter "RefDate" for '2019-1-3' I get only the data for the  '2019-1-3' ==> OK.

But I want additionally a calculated field, that counts the days that

• only with 100 Percent (condition 1)
• only older than 3 days from the selected filter (condition 2)

For condition 1 I found already this solution:

{ FIXED [ID] : SUM( IF [Percent] = 100   THEN 1 ELSE 0 END) }

But how can I additionally add the condition 2 to this calculated field.

Regards

tom

• ###### 1. Re: Calculated Field: Using the filtered Date as a parameter

Create a Date Parameter and then create a calculated field as

Date Parameter = Ref Date

Throw this into the filters and use Parameter to select Ref date.

{ FIXED [ID] : SUM( IF [Percent] = 100 and [REF Date]=DATEADD('day',-3,[Date Parameter])   THEN 1 ELSE 0 END) }

• ###### 2. Re: Calculated Field: Using the filtered Date as a parameter

Hello Tom,

You can use the DATEADD function to subtract 3 days from the selected RefDate and add to the criteria for the date to be less than this value:

Thanks,

Will

• ###### 3. Re: Calculated Field: Using the filtered Date as a parameter

Hi Maneesh,

when I create a calculated field like you have described it I get an Boolean Field (Date Parameter = Ref Date).

Can you describe that in more detail ?

Thx

tom

• ###### 4. Re: Calculated Field: Using the filtered Date as a parameter

Hi Will,

thx for your answer. The problem here is that RefDate is not the date from the filter. It is the date from the respective row in the calculation.

Based on your example I need to have something like:

IF [RefDate] > DATEADD('day',-3, [RefDateUSED_In_The_Filter] )

Thx

tom

• ###### 5. Re: Calculated Field: Using the filtered Date as a parameter

Thomas,

That's correct. You get a boolean field and you put that into the filters and select 'True'.

Now Parameter acts as the [Ref Date] filter selection. Any date you select through Parameter, it filters the ref date based on our above calculated field.

for the second part,

Now the fixed function ignores the quick filters unless we specify the dimension in the fixed calculated field. So, our boolean calculated field won't have effect on it but we're passing parameter within the fixed function so it filters for the 3 old days.

1 of 1 people found this helpful
• ###### 6. Re: Calculated Field: Using the filtered Date as a parameter

VERY VERY COOL :-)

Thx a lot Maneesh !