3 Replies Latest reply on Nov 29, 2016 12:57 AM by lei.chen.0

    Calculated field that looks up on a line by line basis in data source

    Michael Wong

      I'm looking for the necessary functions to produce a more complicated version of the output below below into tableau and essentially I will need the following for the calculated field:

       

      1. Ability to create a calculated field using numbers from 2 separate lines of data

      2. Date and ID to be the same of the entries in which the numbers are being pulled

      3. Exercise portion to look for the previous portion and add two fields from the previous to two field in the current portion

       

      So in cell G3, I hope to get a tableau formula that if exercise portion = "B" it will look for the instance of exercise portion "A" that has the same date and ID as exercise portion "B" and then pull numbers from both those lines to make the calculation.

       

      "Resting Calories Burned" will be the calculated field. Thank you!

       

        • 1. Re: Calculated field that looks up on a line by line basis in data source
          lei.chen.0

          Hello Michael,

           

          I just created a calculated field "Resting Calories Burned".

           

          Please find the details in the attached workbook.

           

          As for further requirements, please attach some sample data.

           

          Regards.

          • 2. Re: Calculated field that looks up on a line by line basis in data source
            Michael Wong

            Thanks Lei, but this doesn't exactly solve what I'm looking for let me add a little bit of what I'm trying to do, I've attached additional data below.

             

            I know what I'm looking for will require hard coding but I'm perfectly fine with that. I'm hoping that I can get a value for "Resting Calories Burned" rather than having to build a table that has the values only when creating a visualization.

             

            Addtl notes:

            -The exercise portions will always run in cycles daily (e.g. in this data      pullups-situps-burpees;      sprints-jumping jacks-shoulder press;       curls-throwing-crunches)

            -If the customer ID changes from entry to entry within the cycle, the calculated field of "resting calories burned" will return a null value

             

            • 3. Re: Calculated field that looks up on a line by line basis in data source
              lei.chen.0

              Hello Michael,

               

              Sorry for my late reply!

               

              Addtl notes:

              -The exercise portions will always run in cycles daily (e.g. in this data      pullups-situps-burpees;      sprints-jumping jacks-shoulder press;       curls-throwing-crunches)

              -If the customer ID changes from entry to entry within the cycle, the calculated field of "resting calories burned" will return a null value

               

              First I noticed that your data row order is important.

              The first order is date, but there is no key for second order, neither "Customer ID" nor "Exercise Portion", so I added a field "No" to your csv to indicate second sort.

               

              Second, for the caculated field "Resting Calories Burned", if the field is continuous, Null is shown as "", if discrete, shown as "Null".

              Please decide which one to use.

               

               

              A sample workbook is attached.

               

               

              Regards