1 Reply Latest reply on Dec 1, 2016 7:10 AM by Stephen Rizzo

    Creating a calculated field "flag" attached to a dimension value

    michael frenzel

      Attached is a tableau file which currently only has information from the excel data source.  I have created the dimension "Promo Change (excel)" manually in the data source, but my question is can I create this using a calculated field instead?

       

      The data source shows sales for 6 products and 2 weeks, and the status shows whether those products were sold at full price or in a promotion.

       

      The promo change flag I wish to create should do the following:

          

           I wish to enter a week number as a parameter (PW)

           For products sold at promo status in PW and at full price in PW-1 the flag should say "New Promo"

           For products sold at promo status in PW and at promo Status in PW-1 the flag should say "Old Promo"

           For products sold at full price status in PW and at promo status in PW-1 the flag should say "Promo turned off"

           For products sold at full price in the PW and at full price in PW-1 the flag should say "Full Price" (although there are non of these in this sample dataset)

       

      I have tried using an if statement to generate this but im getting nowhere.  My difficulty seems to be that I cannot reference the status of the previous week

       

      Is this possible to achieve in tableau, or must I create this in the data source?

      This is my first post, so apologies I have not been clear in my question.

        • 1. Re: Creating a calculated field "flag" attached to a dimension value
          Stephen Rizzo

          How about the following calculated field [Promo Change]:

           

          IF ATTR([Status]) = "Promo" AND LOOKUP(ATTR([Status]),-1) = "Full Price" THEN "New Promo"

          ELSEIF ATTR([Status]) = "Promo" AND LOOKUP(ATTR([Status]),-1) = "Promo" THEN "Old Promo"

          ELSEIF ATTR([Status]) = "Full Price" AND LOOKUP(ATTR([Status]),-1) = "Full Price" THEN "Full Price"

          ELSEIF ATTR([Status]) = "Full Price" AND LOOKUP(ATTR([Status]),-1) = "Promo" THEN "Promo Turned Off"

          END

           

          Set the table calculation to the following:

           

          Compute Using:

          Specific Dimensions: Product, Week, Status (in that order)

          At the level: Deepest

          Restarting every: Product