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

# "Fixed" Function Behaviour

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

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:

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

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.

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

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

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

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

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

• ###### 4. Re: "Fixed" Function Behaviour

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

No problem , I am glad that it helped

Thanks,

Ritesh

• ###### 6. Re: "Fixed" Function Behaviour

You are welcome, Ian.

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

ZZ