6 Replies Latest reply on May 21, 2017 4:06 AM by Zhouyi Zhang

    "Fixed" Function Behaviour

    Ian Holder

      I have Tableau Public at home...if a workbook is needed I'll add one Monday when I'm back at work.

       

      https://public.tableau.com/views/FixedTesting/AllData?:embed=y&:display_count=yes

       

       

      The All Data worksheet shows the data from surveys in Excel plus calculated fields I have added in Tableau.  Surveys are unique for Session and Subject, and a Subject is surveyed over multiple sessions.  Q1 was not introduced until the second session. 

       

      What I want to do is calculate the number of subjects where Q1% is equal to or over 65% [and then do the same for the other questions] and the number where it is less than 65% so I can represent this as a Pie Chart.

       

      Q1% is gotten by dividing the value of Q1 [the number of favourable responses] by the number of responses Received:

      SUM([Q1])/SUM([Received])

       

      The Subject and Session worksheet shows this breakup where my category field [TRUE / FALSE] is:

      IF {FIXED [Session + Subject] : [Q1 %] } >= 0.65 THEN

          "TRUE"

      ELSE

          "FALSE"

      END

       

      where [Session + Subject] is:

      [Session] + [Subject]

       

      This appears to work.  The values line up with what I expect as per the data on the All Data workshet.

       

      However, if I use this formula:

      IF {FIXED [Subject] : [Q1 %] } >= 0.65 THEN

          "TRUE"

      ELSE

          "FALSE"

      END

       

      , as per the Subject worksheet, I get very different values: 3 FALSE and 1 TRUE for each session.  The Subject with Subject tab shows the problems here.

       

      My first question is, why, when I am grouping by Session, does the second formula not work?  And how exactly does it work, and what is it calculating?  I understand Subject is not unique across my data set, but I thought this would be okay because of the grouping in that there is only one occurrence of each Subject per Session.

       

      My second question:

       

      This also seems to work:

      IF {FIXED [Session], [Subject] : [Q1 %] } >= 0.65 THEN

          "TRUE"

      ELSE

          "FALSE"

      END

       

      Is this better than creating a formula as I did for the first function?  Or is it the same thing?   They both seem to work when I take away Session as a grouping and look at the results overall, but I am worrying I am misunderstanding this function so any advice you can give me on how to achieve what I want would be appreciated.

       

      I will add a filter that Q1 >= 1 so I remove the first session once I get this working [see Filtered Sheet], but I've left all records in for now to make my counting easier.

       

      Thanks,

      Ian.

        • 1. Re: "Fixed" Function Behaviour
          Zhouyi Zhang

          Hi, Ian

           

          To your first question, I am not sure if you are familiar with sql query or not. basically, FIXED LOD  is similar to the group by/over partition by which pre-aggregate the data in database level, e.g. in your code

           

          {FIXED [Session + Subject] : [Q1 %] }

           

          which equals to select session, subject, sum(Q1)/Sum(Received) from Table group by session, subject

           

          while you use {FIXED [Subject] : [Q1 %] }

          it equals to select subject,sum(Q1)/Sum(Received) group by subject

           

          so you will see the data has been aggregated in different level.

           

          Now, Fixed loc in Tableau, below is the definition from Tableau help

           

          FIXED Level of Detail Expressions

          FIXED level of detail expressions compute a value using the specified dimensions, without reference to the dimensions in the view.

          FIXED Level of Detail Expressions

          which means no matter what dimensions you drag to the view, the result is already computed.

           

          To you second question, as explained above, no matter Session+subject or session, subject, it will be translated to the same query structure eventually, so it returns the same result.

           

          Not sure my explanation is make sense to you or not, but please let me know if you have any question, Tableau LOD is very useful function for calculating data.

           

          ZZ

          2 of 2 people found this helpful
          • 2. Re: "Fixed" Function Behaviour
            Ritesh Bisht

            Hey Ian ,

             

            Just follow below steps to see the mistake.

             

            Create a Calculation Field

            Fixed=

            {FIXED [Subject] : [Q1 %]}

            Then drag it to your sheet, you will understand

             

            Screen Shot 2017-05-20 at 5.47.37 PM.png

             

            For example if you make Subject DEF456 fixed then calculate [Q1]

             

            Q1%=SUM([Q1])/SUM([Received])

             

             

             

            Screen Shot 2017-05-20 at 5.51.23 PM.png

             

            So as per Fixed that Ans is OK.

             

            Rest you can see in attachment, sorry I have to go somewhere but I guess it clarifies the mistake at-least.

            1 of 1 people found this helpful
            • 3. Re: "Fixed" Function Behaviour
              Ian Holder

              Thank you Zhouyi; a very helpful and easy to understand explanation.

              • 4. Re: "Fixed" Function Behaviour
                Ian Holder

                Ritesh: thank you very much.  Sadly I can only mark 1 answer correct here, as your answer helped immensely.  I was misinterpreting what "Fixed [Subject]" did.  Thank you.

                • 5. Re: "Fixed" Function Behaviour
                  Ritesh Bisht

                  No problem , I am glad that it helped

                   

                  Thanks,

                  Ritesh

                  • 6. Re: "Fixed" Function Behaviour
                    Zhouyi Zhang

                    You are welcome, Ian.

                    and happy to help you understand and enjoy the LOD from Tableau.

                     

                    ZZ