9 Replies Latest reply on Jul 23, 2018 10:19 AM by Hari Ankem

# Contract Periods of Performance - How to Viz???

Afternoon all,

I am using Tableau 10.5 and I am trying to build a chart to visualize contract information.

Some background:

Each contract has a contract award number.  Each contract has an ultimate start and end date.  A period of performance (PoP) is 365 days until the end date is shorter.   Example a contract length is 5 years, therefore each PoP is 1 year for a total of 5 PoPs.  The second contract is only 6 months, therefore the only PoP is 6 months.

What I am trying to build is some sort of stacked bar chart or bullet graph to show the PoP associated with a contract award number.  PoP 1 = Yellow, PoP 2 = Green, PoP 3 = Blue.

Here's an example of what I am trying: http://2.bp.blogspot.com/-J6kDDl5ype8/UiA5gzQq-jI/AAAAAAAAAcE/3yfDKMp-xCg/s1600/a8.png

I want to add a reference line to show today's date and how many days left till the end of the contract in the tool tips.

One of the issues that I am having is that the dataset I am using does not delineate the various PoP's, therefore I had to create them via a calculated fields.

I have attached a workbook to help.  Any suggestions would greatly be appreciated.

Scott

• ###### 1. Re: Contract Periods of Performance - How to Viz???

Does this solution help? The reference line and grey bars indicate the completed period.

1 of 1 people found this helpful
• ###### 2. Re: Contract Periods of Performance - How to Viz???

Yes I think that is what I am looking for.  Can you send me the workbook with your solution?

Thanks,

Scott

• ###### 4. Re: Contract Periods of Performance - How to Viz???

You must have Tableau 2018.1 and I have 10.5.  Can you please save as 10.5 and reattach?

Thank you,

Scott

• ###### 5. Re: Contract Periods of Performance - How to Viz???

10.5 version workbook attached.

• ###### 6. Re: Contract Periods of Performance - How to Viz???

Hari,

I really appreciate your help!  This is excellent work!

Can you please tell me more about your join calculation and how you derived it?  I am very interested to know more as part of my issue is that some of our source data isn't in very good condition and coming up with a period is a challenge for me.

Thanks,

Scott

• ###### 7. Re: Contract Periods of Performance - How to Viz???

OK. Let me try to explain:

1. Our need here is to break the entire contract duration into multiple Periods of Performances with 1 year each, and any remaining balance into a separate period. So, I made an assumption that the longest contract possible will be of 10 years and so formed a list of values with 1-10 as shown below. Note: I didn't consider the 365 days PoP as mentioned by you.

2. I am computing the numbers of years for every contract by taking the difference between the contract end and start dates. Since this value can be less than 1, I am adding 1 to the rounded integer value of the difference for each contract. Example: For Contract 1, the difference is 5 years but we need to split it into 6 PoPs. So, the left side of the join gives us 6, while the right has values from 1 to 10. So, the data now gets multiplied 6 times to give us 6 rows as shown below. I have then created some calculated fields to now break the entire contract period to the individual PoPs.

Hope this helps.

• ###### 8. Re: Contract Periods of Performance - How to Viz???

Hari,

As I look at your solution, it has been hard for me to ingest so I have been thinking about it.  I am trying to not have a solution where someone has a to "go into the viz" and adjust the data.  In your solution, if there is a case where there is more than 10 PoP's then I am forced to go into and "adjust the data".

Trying to keep an open mind about advice - I will try your suggestion and see what happens.

I do know that using the MAX version number, MIN start date, MAX start date gets me the data that I need.  I have been trying to keep this as my goal and wrote this post - How to aggregate a data connection to row level data???

Scott

• ###### 9. Re: Contract Periods of Performance - How to Viz???

Scott,

There may always be some limitations to a solution, or may even have multiple solutions to a problem. In the solution suggested above, you can even put a large number, e.g. 1000, and still the solution would work with no impact to the desired output.

Now, coming to the other post, it's not allowing me to comment on it. What you are looking for is to have a data source filter which is very much possible using LOD's too. In the below images, I have created the calculated field as shown below and added it to the data source filter and you should have the required data. To derive the correct Start Date, I renamed the original "Start Date" to "Start Date - Original" and created another calculated field to get you the desired value.

Hope this helps.

1 of 1 people found this helpful