6 Replies Latest reply on Jun 29, 2011 8:22 AM by Marc Engle

    Creating calculated fields on Parameter/Value data source

    Marc Engle

      I am having trouble getting calculated fields to work like I want.  I am joining three different tables together in my data source, one containing project numbers, one containing project attribute data, and one containing project milestone date data.  I'd like to create a set of calculated fields that shows the delta between certain gate milestones, but when I do that, each row in the joined data table doesn't have the data I'm needing for the full calculation, so they all come out to be NULL instead of an actual value.  An example of my joined raw data table is attached (colors represent the data coming from each individual base table)

       

          What I'd like to be able to do is to have a calculated field that contains the number of days between gateid's 1 and 5's realized dates (beginning to end of project) and then some of the interim values as well (between 1 and 2 or 2 and 3, etc).  I am then plotting those values for all projects of a certain type as an average for analysis.

       

          Can anyone help me with how to get a delta value between two gates when the data is in this format?  I can do it if I rotate the data source through a database view and basically turn the data table into a "flat" table format, but that leads to some problems with my other viz's on my dashboard.  I currently have two different data sources, one in the flat format and one in this rotated format on the same data, but that also has its own problems when trying to use a quick filter for an attribute to apply over all data sources, which I also can't figure out how to do. 

        • 1. Re: Creating calculated fields on Parameter/Value data source
          Joe Mako

          Can you provide a sample packaged workbook that represents your situation?

          • 2. Re: Creating calculated fields on Parameter/Value data source
            Marc Engle

            Sure, Joe.  Here is the attached workbook with dummy date.  I would expect to see a bar graph segmented by color indicating the different times between the gates for the two different projects.  When I rotate the data to a flat format, that's what I'm able to get because the calculations work correctly.  Thanks for any help you can provide.

            • 3. Re: Creating calculated fields on Parameter/Value data source
              Joe Mako

              Are you looking for something like the attached?

              • 4. Re: Creating calculated fields on Parameter/Value data source
                Marc Engle

                Thanks for that workbook, Joe.  It's very close to what I'm after.  One viz that I want to create from the data can now be generated using this method.  The other viz, however, is similar, but it looks like the use of the MAX function to get the dates could be problematic for me. 

                 

                That other viz is the same type of stacked bar showing the deltas between dates, but at a higher level of aggregation, like a particular attribute's values or product line instead of being by project number itself.  The viz requires that I throw the product line of my project on the rows shelf instead of the project number itself so it would average the times between the gates for all projects in a particular product line (the real data of course is a much larger dataset).

                 

                Does that make sense?  I think this almost has me there, but I can't find a different aggregating function that lets me pull the date value on an individual project basis.  It's almost like I need a collector that is smart enough to only give me a value for each project (hence the data rotation).

                • 5. Re: Creating calculated fields on Parameter/Value data source
                  Joe Mako

                  Yes, what you are looking for is very doable in Tableau with custom table calculations. Maybe something like the attached is what you are looking for.

                   

                  In the attached on sheet "Per Project Number" you can see the blue bar marks are 7.14 and 4.86, and on the "Sum All Table Calc", the blue mark has a value of 12.00 this is equal to 7.14 + 4.86

                   

                  In the calculations, you can change the WINDOW_SUM to WINDOW_AVG to get the average instead of the sum. I have the field "vcProjectNumber on the Level of Detail shelf, and that is the level of aggregation for the MAX() calculations, and by setting the compute using for the table calculations to that field, we sum up all those values with the table calc. If you now add another dimension (blue pill) to the worksheet, you will now partition with this new dimension. For example, currently it is all sum of all projects, and if you added a dimension for product line, you would get the sum of projects within each product line.

                   

                  There are many other interesting things that can be done with table calcs, and many ways to employ them.

                  • 6. Re: Creating calculated fields on Parameter/Value data source
                    Marc Engle

                    Thanks so much for your help, Joe.  Looks like I need to bone up on the custom table calculations as they appear extremely powerful!  I appreciate you taking the time to explain how you did the calculations and I believe I can get exactly what I need from here.