0 Replies Latest reply on May 12, 2018 8:34 AM by Pratiksha Autade

    Tableau Override including circular referencing formula

    Pratiksha Autade

      Hi,

       

      I have original values in the database which I want to override using parameters on Dashboard. All the KPI's derived using this override values from parameter contains circular referencing formula or needs output of its own calculation as input for next cycle. Eg:

       

      I get Production and Sales as Input from parameters. And factors as input from database.  Rest all existing KPI's in the database should get replaced by new values derived using the new Production and Sales number  if any from parameter entries.

      Sequence for calculations is carried in the following order:

      1) Departures

      2) Unbuild/unshipped

      3) Arrivals

      4) Intransit

       

      NF Arrivals:(Calculated field)

      IF ATTR([Override Month] = MONTH([Date]) ) THEN

      ([NF DEPARTURE])*AVG([Arrival Factor])+LOOKUP(SUM([NF Intransit]),-1)

      ELSEIF ATTR( MONTH([Date]) < [Override Month] ) then SUM([Arrival Qty])

      ELSE

      ([NF DEPARTURE])*AVG([Arrival Factor])+LOOKUP(SUM([NF Intransit]),-1)

      END

       

      NF Intransit: (Calculated field)

      IF ATTR([Override Month] = MONTH([Date]) ) OR ATTR( MONTH([Date]) > [Override Month] )

      then ([NF DEPARTURE]-[NF Arrival])+LOOKUP(SUM([NF Intransit]),-1)

      ELSE SUM([Intransit Qty])

      END

       

      I am getting an error that says "Field contains a circular reference"

       

      Is their any way to solve the above circular referencing error as well as using output of a calculated field as an input for its next iteration.

      Attached is the packaged workbook (version-10.3.2) and excel sheet-  test that includes formula to help understand the logic.