9 Replies Latest reply on Mar 31, 2017 11:30 AM by Jason Hanser

    FIXED Calculation is Messing Up

    Jason Hanser

      Attached is a simple workbook that displays the demographics of a university overtime. The end result is to have a table that shows the demographics of the entire university as well as the demographics of incoming class for each term. To generate the values for the entire school, I used the FIXED function. However, the calculated values for the FIXED function are different for New Students set, despite that dimension not being included in the FIXED calculation.

       

      If you open the workbook, you will see that the "FIXED COUNT, In" value for Asian is null, while the "FIXED COUNT, Out" value is 32. It only seems to affect certain races and certain terms.

       

      I'm at my wits end with this issue. I built an entirely new workbook for scratch, hoping I would catch my mistake in the process but to no avail.

       

      Can someone spot my error?

       

       

      EIDT: I should mention that I checked my Query and that is not the issue.

        • 1. Re: FIXED Calculation is Messing Up
          David Li

          Hi Jason, is this what you're looking for?

          I changed your LOD to this:

          {FIXED [RACE_ETHN], [TERM]: SUM(IIF([STU_ATTR]="NEW",1,0))}

          Then, I removed the new student breakdown and just used the LOD calc as the new student calc.

          • 2. Re: FIXED Calculation is Messing Up
            Jim Dehner

            hi Jason -

             

            You need to include the new student check in your LOD    {FIXED [RACE_ETHN],[STU_ATTR], [TERM]:  COUNT([Number of Records])}

             

            Here is what was going on with your calculation   {FIXED [RACE_ETHN], [TERM]: COUNT([Number of Records])}  this is like saying -

            Remember that FIXED calculations happen above the level of the table

            Looks at each term and each race in the data set and come up with a total that is fixed for the combination -

            Your table below then uses that "fixed total" anytime the combination comes up in the table

            but note the total is not place in the table if there is is no record in your data for that combination ( see 2017 Asian - in)

            if you look at the underlying data there is no record for NEW Asian Students in 2017 - therefore there is no entry in the table

             

            on the other hand 151 Hispanic students show up in IN and in OUT but there are only 151 total - they entry is in each part of the table because there is at least 1 new and 1 returning student

             

             

             

             

            The revised calculation returns the actual count fixed at the term, race, New/Return student level

             

             

            Does the explanation help?

             

            Let me know

            Jim

            1 of 1 people found this helpful
            • 3. Re: FIXED Calculation is Messing Up
              Jason Hanser
              but note the total is not place in the table if there is is no record in your data for that combination ( see 2017 Asian - in)

               

              Ooooooooohhhhh, I got it now. That still seems weird, but I get why only some of the values are messing up.

               

               

              The revised formula isn't right (the above poster fixed that), but the explanation of the error is helpful.

              • 4. Re: FIXED Calculation is Messing Up
                Jason Hanser

                Mostly. But how do you get subtotals for each of the Measure Values?

                 

                Rather than displaying both of the totals at the bottom, it would be nice to have them at the bottom of their respective section. Does that make sense? The sub-total function is greyed out.

                • 5. Re: FIXED Calculation is Messing Up
                  Jim Dehner

                  right you are - i posted the wrong view -

                   

                  The totals line (and subtotal are pulled in from the analytics tab - they are not calculated fields in themselves -

                   

                  also I should have mentioned Include and Exclude work at the level of the viz

                   

                  jim

                   

                   

                  • 6. Re: FIXED Calculation is Messing Up
                    Jim Dehner

                    see below

                     

                    You go to the analytics tab the drag Totals on to the Viz and the pop up box will open and select Sub-totals

                     

                    Also for future reference - if you have a deeper hierarchy you can select the level or levels where you want subtotals

                     

                    Jim

                     

                     

                    • 7. Re: FIXED Calculation is Messing Up
                      Jason Hanser

                      "You go to the analytics tab the drag Totals on to the Viz and the pop up box will open and select Sub-totals"

                       

                      That doesn't work for me. The subtotal option is greyed out. See attached workbook.

                      • 8. Re: FIXED Calculation is Messing Up
                        David Li

                        Unfortunately, there's no way to show the totals for each measure right underneath its pane except to break up the sheet into multiple sheets and stack them in a dashboard. Subtotals don't really apply to Measure Names/Values, only to multiple levels of true dimensions.

                        • 9. Re: FIXED Calculation is Messing Up
                          Jason Hanser

                          Gotcha. I think I figured out a fix, which was to create a dummy set

                          containing all students and adding that to the row dimension.

                           

                          Jason Hanser

                          Research & Analytics Associate, Institutional Effectiveness

                          Eckerd College

                           

                          p (727) 864-7888

                           

                           

                          CONFIDENTIALITY NOTICE:  This message and any attachments are for the sole

                          use of the intended recipient(s) and may contain confidential and

                          privileged information that is exempt from the public disclosure. Any

                          unauthorized review, use, disclosure or distribution is prohibited. If you

                          have received this message in error, please contact the sender (by phone or

                          reply electronic mail) and destroy all copies of the original message.

                           

                          On Fri, Mar 31, 2017 at 1:54 PM, David Li <tableaucommunity@tableau.com>