12 Replies Latest reply on Sep 22, 2016 1:09 PM by David Li

# Creating a histogram for counts of correct responses

I have data that is like this:

User ID     | Survey ID          | Question type             | Correct

23414       | A                  | 1                         | True

23414       | A                  | 2                         | True

23414       | B                  | 1                         | False

23414       | B                  | 2                         | True

28994       | A                  | 1                         | True

28994       | A                  | 2                         | True

28994       | B                  | 1                         | False

28994       | B                  | 2                         | False

...etc.

I am looking to create four histograms. Each histogram counts number of users, where each count corresponds to a range in the number of 'correct' responses: Four of these histograms are laid out vertically so that the rows are partitioned into Survey ID, then Question type:

Tableau worksheet mock-up

Survey     QType                    Histogram

A               1                  (Chart goes here)

2                         (and here)

B               1                              (Yay)

2                         (That's it)

To create the field "# correct responses', I have a calculated field whose formula is:

SUM(if [Correct] then [Number of Records] end)

...but I can't create bins out of such a calculation. So I added an LOD:

{FIXED [User ID]: SUM(if [Correct] then [Number of Records] end)}

But then the details from 'Survey' and 'Question type' are left out, i.e. all four histograms look the same because they are each totaling up the # of correct responses across all surveys and all questions.

Two questions:

1. Why can't I create bins out of the first calculation? SUM(if [Correct] then [Number of Records] end)

2. What should replace the FIXED level-of-detail calculation so that each histogram is relative to a survey and a question type?

• ###### 1. Re: Creating a histogram for counts of correct responses

Hello Matt...

Could you give us a TWBX to work with?

• ###### 2. Re: Creating a histogram for counts of correct responses

Hi Matt!

1. The [Number of Records] field is special because it's basically already an aggregation calculation. You should just replace it with the number 1 to get the desired result.
2. You need to add in the other dimensions, i.e. FIXED [User ID], [Survey ID], [Question type]

EDIT: I erased the question I had asked because I see now that it wouldn't work.

• ###### 3. Re: Creating a histogram for counts of correct responses

Yeah, just fixed up a share-able one! Here it is.

• ###### 4. Re: Creating a histogram for counts of correct responses

I'm not sure on why I'm using the condition inside the SUM... I just like it this way, but let me think about that question. Does it affect the solution in any way?

Your second suggestion works, is that the best solution? Is there any other alternative?

• ###### 5. Re: Creating a histogram for counts of correct responses

Does this look right? I switched your LOD calculation to:

{ FIXED [User ID], [Survey ID], [Question type]: SUM(IIF([Correct],1,0))}

EDIT: There are some alternatives, but they're much less elegant. See here: Aggregate Bins / Summarized Bins | The Don Data

• ###### 6. Re: Creating a histogram for counts of correct responses

The only problem I think is the actual count - it is multiplying each number by 5, i.e. the A-1 cell should have a count of 5, not 25.

There are 5 users for each survey/question type, and each user has 5 questions corresponding to that nested category. Either one of these numbers could be resulting in the multiplication. Seems like a by-product of pivoting. This is now the thing I am the most stuck on.

• ###### 7. Re: Creating a histogram for counts of correct responses

It's definitely the fact that there are 5 users in each category.

• ###### 8. Re: Creating a histogram for counts of correct responses
1 of 1 people found this helpful
• ###### 9. Re: Creating a histogram for counts of correct responses

Great, thank you!

• ###### 10. Re: Creating a histogram for counts of correct responses

You're welcome! Glad I could help!

• ###### 11. Re: Creating a histogram for counts of correct responses

David,

Do you know by any chance how I would replace my FIXED calculation with a formula that uses INCLUDE/EXCLUDE so that a quick filter can be used on a dimension that is not one of the three currently involved?

• ###### 12. Re: Creating a histogram for counts of correct responses

You won't be able to use INCLUDE or EXCLUDE with this technique, because FIXED is the only one that will give you an actual row-level, non-aggregate calculation, and the histogram dimension can only work with row-level values. However, if you're just looking to do some filtering, you could try making that quick filter a context filter (by right-clicking and pressing "Add to context"). Context filters and data source filters will affect all LOD calcs.

1 of 1 people found this helpful