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

FIXED Calculation is Messing Up

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

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

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
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

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

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

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

"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

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

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