6 Replies Latest reply on Oct 11, 2018 9:15 AM by tanvi khanna

# Need help with Custom date calculations

Hi All,

My dates are in form of semesters (known as waves) - Sem1 2017, Sem2 2017, Sem1 2018 etc.

I need to calculate improvement within several measures by semester ie performance of in sem1 2018 vs sem2 2017..

As soon as I convert my column 'Wave' = semester from string to date format, Tableau reads it as follows:

sem1 2017 = Jan 2017

sem2 2017 = Feb 2017

sem1 2018 = Jan 2018

I have created 'latest wave' and 'prior wave' columns in data for calculating difference in measures, however since sem1 2018 is the latest wave (Jan 2018 in Tableau). the prior wave is read as Dec 2017 instead of Feb 2017 -> and I do not have data for Dec 2017 but as entire semester.

Any help will be highly appreciated!

Thanks.

• ###### 1. Re: Need help with Custom date calculations

Hi Tanvi,

could you please share mock-up data .

• ###### 2. Re: Need help with Custom date calculations

Hi Sure,

steps I have done:

converted the 'wave' column from string to date with case statements. now dates are 1 Jan 2017, 1 July 2017 and 1 Jan 2018 as I have semester data with me.

what is to be achieved:

I need to make a tree map with hierarchy region->BU and label by improvement of measures 1,2,3 from latest wave to prior wave.

that means I need the values of measure 1,2,3 in latest wave and substract from prior wave. so I created columns latest wave and prior wave to get them dynamically but now confused how to get the difference of values among wave for that measure.

Thanks!

Tanvi

• ###### 3. Re: Need help with Custom date calculations

Hi,

I created columns: latest wave, prior wave. Now I wanted to make another column for storing the measure (CI) when its the latest year/wave.

since Client Index is an aggregated measure ie average of some columns it does not let me make this.

Can you suggest here?

Thanks

Tanvi

• ###### 4. Re: Need help with Custom date calculations

See attached workbook.

I added a "relative date" calculation to compute only the current semester and the last semester.

I used a table calc along that dimension to compute the difference.

I hid the past relative date.

By the way, converting your semesters to dates isn't really necessary. You could instead just parse out the year and number and just use some math like year * 2 + semester to number all semesters. This might be a bit faster but won't matter unless you have a lot of data.

Dan

1 of 1 people found this helpful
• ###### 5. Re: Need help with Custom date calculations

update - I tried this with attr() and it worked! hope this is helpful for others following this post.

Thankyou!

• ###### 6. Re: Need help with Custom date calculations

Hi Dan,

Thank you for the solution.

I have aggregated measures so a bit complex instead of just a measure1.

but the solution (relative dates) works! Thank you so much.

Tanvi