4 Replies Latest reply on Aug 8, 2017 5:03 AM by Rebecca LoSurdo

# Create a Dynamic Calculated Field That Responds to Column Values

Good Afternoon,

I have a data set consisting of dated entries that need to be reported based on specified date ranges (i.e. of the data set population, return the value between dates A and B, B and C, etc.).

The date ranges are relative to each client depending on their start date and are referenced as "Baseline Date," "6 Month Reassessment," "12 Month Reassessment," etc. As such, they exist in my workbook as calculated fields based upon a value given for each client ("Baseline Date").

I have attached a sample workbook to show how we began to go about this. We have calculated each required data element relative to each date range but as we have a large number of elements to report on (far more than the 2 here) and a large number of date ranges to cover, creating a calculated field per element per date range is unsustainable and less elegant than our previous Tableau solutions.

It seems to me that there should be a way to create a single calculated field per element that responds to the date range provided. In other words, one calculated field for BMI that shows the Baseline BMI when the Baseline pill is on the Columns shelf, the 6 Month BMI when the 6 Month pill is on the Columns shelf, and so on. As the date range pills are not part of the original data and are calculated fields themselves, I'm at a loss as to how this could be done.

Any help would be greatly appreciated. I am using 10.1.

• ###### 1. Re: Create a Dynamic Calculated Field That Responds to Column Values

How about not returning an actual measure for each calculation, but rather something like:

IF DATEDIFF('month', [Baseline Date], [Appointment Date]) = 0 THEN "Baseline Date"
ELSEIF DATEDIFF('month', [Baseline Date], [Appointment Date]) <= 6 THEN "6 Month"

ELSEIF DATEDIFF('month', [Baseline Date], [Appointment Date]) <= 12 THEN "12 Month"

END

Then just drop this on Columns, Measure Names on Rows, and Measure Values on Text. You could use additional logic if the 12 month is supposed to also include the values in 6 month, etc (potentially multiple sheets with different date range filters), but it allows you to create a calculation which determines the range agnostic of the measure type, which prevents you from having to create multiple calculations for every single measure.

1 of 1 people found this helpful
• ###### 2. Re: Create a Dynamic Calculated Field That Responds to Column Values

This is perfect. Thank you!

• ###### 3. Re: Create a Dynamic Calculated Field That Responds to Column Values

Glad it helped! If you will mark the answer as correct it will help others find the correct answer more easily in the future as well as marking the question as solved

• ###### 4. Re: Create a Dynamic Calculated Field That Responds to Column Values

Thanks; I'm new here.