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

Need help with a Table Calculation

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 FilmType genre CourseName LectureTitle 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/2016 Episode Science Chemistry, 2nd Edition Basic 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/2016 Episode Science Chemistry, 2nd Edition Converting 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

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

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