4 Replies Latest reply on Nov 7, 2018 8:44 PM by Jon Syu

    I Still Need Help Understanding "FIXED" (Level of Detail)

    Jon Syu

      I'm just learning Tableau on my own, and have no prior experience with spreadsheets, databases, excel, etc.  While most of it feels pretty intuitive, I have some huge holes in my basic knowledge--especially regarding LOD in calculations.  I thought I understood "FIXED", but I'm getting very bad results.  I have a table with "ID" and "Event" in columns.  2 possible values in "Event" are "call" and "email".  All I want to do is find all the IDs that haven't called or emailed yet. 

       

       

      When I use these calculation:


      {FIXED [ID]:MIN(

      IF [CallsEmail]=='none'

      THEN [ID]

      END)}

       

      where [CallsEmail] is this calculation:

       

      {FIXED [ID]:MIN(

      IF [Event]=="call"

      OR [Event]=="email"

      THEN "some"

      ELSE "none"

      END)}

       

      it doesn't work.  I'll look at the data for a particular ID, and it'll show several chats or calls.

       

       

       

      So I tried this calculation instead:

      {FIXED [ID]:MIN(
      IF
      [Event] <> "call")}

      AND

      {FIXED [ID]:MIN([Event] <> "email")}

      THEN [ID]

      END)}

       

      but it didn't work either.  I also had a calculation that found ID with calls and emails, so I made another calculation:

       

      {FIXED [ID]:
      MIN(

      IF NOT CONTAINS([Caller],[ID])

      AND NOT CONTAINS ([Emailer],[ID])

      THEN [ID]

      END)}

       

      but still didn't work.  I tried a couple of more variations, but they're all about the same.  I thought FIXED meant that it would go through every ID?

       

      I can't post the data, so I described it as accurately as possible (I don't think there's anything important I left out).

       

      What am I missing?  I've tried reading different explanations of LOD, but I must be misunderstanding it at a very fundamental level.  Maybe if someone could ELI5 (explain like I'm 5--or maybe like I'm 25 but haven't worked with data and spreadsheets before )?  Also, if you have any good free and online resources for learning Tableau (from zero experience to getting a job, hopefully), it would also be greatly appreciated.  Thank you in advance! 

        • 1. Re: I Still Need Help Understanding "FIXED" (Level of Detail)
          alison.whitaker.0

          Hello

           

          I did a very basic trial and I think what you need is something like:

           

          IF { FIXED [ID]: COUNTD([Event]) } = 0 THEN [ID] END

           

          LODs can be a bit tricky to start with, but I'll try to do the ELI5 explanation!

           

          At the most basic level, you need to think about your dimensions and measures. The FIXED part is a way of dividing up your data into chunks, and this needs to be a dimension. The dimensions always go on the left hand side of the colon. Then you need to choose a measure to aggregate. What is it do you want to measure and at what aggregation?

           

          So the format is something like { FIXED [Dimension]: Sum([Measure]) }

           

          you have the dimension (or dimensions), a colon, your aggregate (e.g. Sum, Avg, Countd, etc.), and then your measure.

           

          In your example, we are using a nested LOD which is a step up from this. If we take the LOD on its own, all I have done is said, for each ID, I want to count how many events happened. I have assumed that where no events have happened, you are interested! Then once we have this, all we are doing is checking if any IDs have no events, and returning that ID.

           

          An explanation I think is a good one can be found here: What’s new in Tableau 9.0? Part 2 – Level of Detail Expressions | Bora Beran

           

          I hope that helps and good luck!

           

          Alison

          1 of 1 people found this helpful
          • 2. Re: I Still Need Help Understanding "FIXED" (Level of Detail)
            jon syu

            Thank you!  I'll check out the link, but your explanation is very helpful.  I'll give it a shot now.  The only difference is, "call" and "email" aren't the only values, so I'll have to do something like

             

            IF {FIXED[ID]:COUNTD([Event]=="call")} == 0
            AND {FIXED[ID]:COUNTD([Event]=="email")} == 0
            THEN [ID]
            END

             

            Hopefully this works.  Thanks again

            • 3. Re: I Still Need Help Understanding "FIXED" (Level of Detail)
              Jim Dehner

              Hi Jon

              I have a grandkid at that age so I will give it a try

               

              Fixed (and the other LOD) calculations are working to creating another layer in your dataset (its not really there - just virtual)

              Fixed will take the dimensions that precede the colon(:), make all the combinations of the dimensions and then perform the aggregation based on what follows the colon -

              Those results form that added layer in the data - it is important to note that the new layer is not an aggregate and can be used in any type of calculation _ table, LODs or simple calculated fields

               

              In words your first formula says   - for each ID  if callsemail = 'none' then return the ID if not return null 

               

              so your formula will return something like this

               

              if you filter out the nulls you get this

               

              Your actual problem is only slightly more complex - but you are still filling a field with Nulls -

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              1 of 1 people found this helpful
              • 4. Re: I Still Need Help Understanding "FIXED" (Level of Detail)
                Jon Syu

                That worked, thanks!

                 

                I still don't completely understand why the other calculations failed though.  This one, for example:

                 

                {FIXED [ID]:MIN(
                IF
                [Event] <> "call")}

                AND

                {FIXED [ID]:MIN([Event] <> "email")}

                THEN [ID]

                END)}\

                 

                was creating "false positives".  Was it checking the same ID/Event combination each time?  In other words, I wanted it to go through the table like so:

                 

                 

                -ID#1,Event#1 -> was event call or email? -> no/no
                -ID#1,Event#2-> was event call or email? -> no/no
                -ID#1,Event#3-> was event call or email? -> yes/no
                -because it one of the events was a call or email, do not return ID#1

                -move on to next ID and repeat

                Would including [Event] in the LOD also accomplish this?  i.e.  {FIXED [ID], [Event]:COUNT(example)}