4 Replies Latest reply on Dec 1, 2016 4:39 PM by Sebastian Cruz

    Help with functions - Min Max LOD Fixed

    Sebastian Cruz

      Hello everybody,

      I am new in Tableau but I was researching about all the features that this software provide.

      I am working on a report but I have some problems... I will really appreciate your support.

       

      First of all, I am working with a server connected to Tableau, but to make my case understandable I copied a portion of data in an Excel file (attached Sample data Tableau)

      I already made this report in excel, but I need to replicate it directly in Tableau.

       

      Output:

       

      I need to get just 1 row per Student UID, and it has to contain the First Math Course taken and the Period related, also the Last Math Course and the Period and Final grade linked with this value.

      I get the LABEL Column

       

       

      First attempt TABLEAU:

       

      I just filtered the Course numbers and the Subject Desc. that I need for my report.

      When I use MIN and MAX functions looks good, but when I add the Final Grade dimension to the Rows it splits a lot of grades, but I just need the grade linked with the Max Course and Max Academic Period.

      So researching on the web I found a function called FIXED, but this function doesn't work if I try to put something like this:  {fixed [Student Warehouse Entity Uid], Min([First Math Course]): MIN([Academic Period])}

       

       

       

      Then, I was trying to work with the LOD Fixed for each column of the table... but doesn't work in all my cases. Because some results are not considering the filter Course Number and Subject Desc.

       

       

      I would lake to get the same output than the Excel (Final Report Sample)

       

      Thanks in advance for your support,

        • 1. Re: Help with functions - Min Max LOD Fixed
          Shinichiro Murakami

          Sebastian,

           

          Thank you for very detail explanation.

          To tell the truth, I did not have time to go thru the detail, but I suspect one thing.

          Could you try to change the filters to "context"

           

          Thanks,

          Shin

           

          • 2. Re: Help with functions - Min Max LOD Fixed
            Bora Beran

            I need to get just 1 row per Student UID, and it has to contain the First Math Course taken and the Period related, also the Last Math Course and the Period and Final grade linked with this value.

             

            [Period of First Math Course]

            {fixed [Student Warehouse Entity Uid] : MIN(IF [Subject Desc]=='Mathematics' THEN [Academic Period] END)}

             

            [Period of Last Math Course]

            {fixed [Student Warehouse Entity Uid] : MAX(IF [Subject Desc]=='Mathematics' THEN [Academic Period] END)}

             

            [Name of First Math Course]

            IF [Period of First Math Course]==[Academic Period] AND [Subject Desc]=='Mathematics' THEN [Course Number] END

             

            [Name of Last Math Course]

            IF [Period of Last Math Course]==[Academic Period] AND [Subject Desc]=='Mathematics' THEN [Course Number] END

             

            [Grade]

            IF [Period of Last Math Course]==[Academic Period] AND [Subject Desc]=='Mathematics' THEN [Final Grade] END

             

            You can drag all of them into a view and use them as dimensions with MAX aggregation. Since all but one value for a given student will be non-null, MAX will pick that value. E.g. MAX(Grade), MAX([Name of Last Math Course]) and then drag [Period of First Math Course] onto filter shelf and exclude NULLs. This will remove students that never took a Math class.

            • 3. Re: Help with functions - Min Max LOD Fixed
              Sebastian Cruz

              Thanks for your time and contribution Shinichiro! I did not know about "Add to context", it helped me a lot.

              Regards!

              • 4. Re: Help with functions - Min Max LOD Fixed
                Sebastian Cruz

                Thank you Bora!, this is exactly the output that I needed!

                I really appreciate your support.

                Regards!