3 Replies Latest reply on Dec 6, 2013 1:02 PM by cory.tak.0

    Circular Referencing Calculated Field?

    Lucas Luboff

      Hi Tableau Community,

       

      I have a quick question about the way Tableau does its Calculated Field Calculations and its requirement of massively flat files for data manipulation.

       

      So if I have data structured as below:

       

      build dateDays In ServiceModelIDP&L Cost
      12/2/2012718Other8980
      5/3/2012861Other39637.5
      7/18/2013977Other67190
      9/6/20131110Other597525.78
      8/15/201310Other83346.2
      10/13/201325Other43530.03

       

      And I want to write a calculated field that looks like this:

      IF ([Days In Service]) > [Days In Service] THEN COUNT([ID]) ELSE 0 END

       

      What I would like to do is count all the IDs that have more Days In Service than when Days In Service is placed on the X-axis for each data point.  As I understand, I would need to duplicate all records for each Day in Service for all records in order to make this calculation work. Making the data set look something like this:

       

      DISbuild dateDays In ServiceModelIDP&L Cost
      112/2/2012718Other8980
      15/3/2012861Other39637.5
      17/18/2013977Other67190
      19/6/20131110Other597525.78
      18/15/201310Other83346.2
      110/13/201325Other43530.03
      212/2/2012718Other8980
      25/3/2012861Other39637.5
      27/18/2013977Other67190
      29/6/20131110Other597525.78
      28/15/201310Other83346.2
      210/13/201325Other43530.03
      312/2/2012718Other8980
      35/3/2012861Other39637.5
      37/18/2013977Other67190
      39/6/20131110Other597525.78
      38/15/201310Other83346.2
      310/13/201325Other43530.03

      And the formula would then look like this:

      IF ([Days In Service]) > [DIS] THEN COUNT([ID]) ELSE 0 END

       

      Is there any other way to do a circular referencing calculation like this in Tableau without creating this new DIS field and massively duplicating my data set?

       

      Thanks for taking a look!

        • 1. Re: Circular Referencing Calculated Field?
          cory.tak.0

          Hi Lucas,

           

          It's a little difficult to know exactly what you're looking for without a workbook. However, if in your second data set you created a flag for each record that indicated whether or not it was greater than another 'Days In Service' record (1) and collapsed those records together, you would essentially have a rank (ID 597's 'Days in Service' would have five 1's and the total would be 5). Using the index function and a table calculation in 8.0, I've demonstrated this by using a calculated field that performs its function Table (Down) and sorts by Days In Service Ascending. (I believe 8.1 actually has the ability to rank directly without using the Index() Table Calculation).

           

          If this is what you're looking for, table calculations can provide a great way to perform these analyses without necessitating a Cartesian product.

           

          I hope this helps,

           

          Cory

          • 2. Re: Re: Circular Referencing Calculated Field?
            Lucas Luboff

            Hi Cory,

             

            Thanks for your input.  I don't think this is exactly what I am looking for.  Let me give you a better explanation of my problem.

             

            Based on the first data set I was trying to create a line graph which used the first calculated field to count how many IDs have more days in service than when Days in Service is plotted as a continuous column value for the x-axis.

             

            I've re-posted your workbook with what I am attempting to do, but I don't think Tableau can do this without changing my underlying data set to the second set I posted, the Cartesian product. I added that as a new data source and created what I wanted to do with the original data set with this new one on Sheet3.  Sheet2 uses the original data set, but I can't seem to wrap my head around a way to do this with the calculated fields you created.  The calculated fields in the second data set accomplish this, but it requires the Cartesian product.

             

            Is there any way to do this with the original data set you used to try and replicate my problem?

             

            Thanks,

            Lucas

            • 3. Re: Re: Re: Circular Referencing Calculated Field?
              cory.tak.0

              Hi Lucas,

               

              It looks like this table calc is still what you want to see. The tricky part is that with table calcs it refers to performing a calculation within the current window, so when we moved away from the cross tab, the pill turned red because it was referencing fields that were no longer in that window.

               

              TableCalc.png

              The key for this solution is to make sure the table calc is sorting by the days of service and descending. I hope this helps answer your question. If not, we'll keep working at it or maybe one of the brilliant people on here can help us out.

               

              Cory