2 Replies Latest reply on Dec 14, 2016 5:17 PM by Ujval Gandhi

    Need help with a Table Calculation

    Ujval Gandhi

      This is the data that I have

       

      I want to create three initial columns using calculations

       

      First Genre Watched : it should look at film type = Episode and say the first genre watched is "Science" for all the records

      First Course Name : Chemistry : 2nd edition

      First Lecture Title : Basic Concepts of Quantitative Reasoning

       

      I have the entire set in Excel.

      I gave it a shot using LOD and Lookup but could not make it work. Any  help is greatly appreciated !

       

       

       

       

       

      Date
        Played
      FilmTypegenreCourseNameLectureTitle
      4/26/2016
      4/27/2016
      4/28/2016
      4/29/2016
      4/30/2016
      5/1/2016
      5/2/2016
      5/3/2016
      5/4/2016EpisodeScienceChemistry, 2nd EditionBasic Concepts of
        Quantitative Reasoning
      Quantitative Reasoning in Chemistry—Density
      Quantitative Reasoning in Everyday Life
      5/5/2016
      5/6/2016
      5/7/2016
      5/8/2016
      5/9/2016EpisodeScienceChemistry, 2nd EditionConverting between
        Systems of Measurement
      Elements, Atoms, and the Periodic Table
      Ions, Compounds, and Interpreting Formulas
      Isotopes and Families of Elements
      Quantitative Reasoning in Chemistry—Density

      The SI (Metric) System of Measurement

       

        • 1. Re: Need help with a Table Calculation
          Stephen Rizzo

          A LOD expression works here, but its messy:

           

          [First Genre Watched] : {FIXED : MIN(IF [Date Played] = {FIXED : MIN(IF NOT(ISNULL([Genre])) THEN [Date Played] END)} THEN [Genre] END)}

           

          To break this down, we start by finding the dates where [Genre] is not null and take the minimum of those in the entire data set. That tells us which row we need to look at. Then we find the genre value for that row, null out all other genre values, and use another LOD expression to replicate it throughout the column. This can be done for each of your fields.

          2 of 2 people found this helpful
          • 2. Re: Need help with a Table Calculation
            Ujval Gandhi

            Thank you so much !!!! This was perfectly what I wanted. Yes, messy but works like a charm

             

            I also learnt how you set up the calculation, would be able to reuse it for other calculations

             

            Regards,

            Ujval