1 2 Previous Next 20 Replies Latest reply on Jan 29, 2013 8:18 AM by Chris Turnbull

# Matrix\Grid Calculation Help

Hi All

Hopefully you can give me a little help with my calculation struggles with the attached.

I have attached a workbook of where I am at and a Sporeadsheet of where I want to be.

I cannot workout how to do the following from the Spreadsheet:-

1) Number achieving expected progress - Calculated by totalling the darker green cells for each row.  Which in effect is the Total of all Cells with better than Progress of > 2

2) School % achieving percentage Expected Progress - Calculated by doing a % Calc on 1 above and the Cohort\Total Number of Pupils

I will blend the data to add the National % Achieving Expected Progress and I will then need  to workout Number exceeding national progress which is Progress > 3 abd do the last 2 %'s on these numbers.

I would really appreciate any help as I have been doing battle with for some time and exhausted all I know at this stage in my Tableau Learning.

Thanks

Chris

• ###### 1. Re: Matrix\Grid Calculation Help

Chris,

Looks like you're almost there... You just need to make some extra calculated fields to only count the records where progress > 2...

Does the attached workbook help?

Robin.

• ###### 2. Re: Matrix\Grid Calculation Help

Robin

That is fantastic.

Thank you so much for your help - I have struggled with how that Calculation works.

Please find attached my latest nearly finished effort - all I am stuggling with now is a bit of colouring on 2 columns.

I need to compare School percentage achieving Expected progress against National Percentage achieving expected progress and colorise the school % Calc Red or Green Based on whether it is above or below.  Same with School % Exceeding v National % Exceeding.

Is it also possible have grand totals at the bottom Columns and Colorise them or is there a workaround?

Hopefully you can help me with that but if you can't thank you so much.

Chris

• ###### 3. Re: Matrix\Grid Calculation Help

Hi Chris,

You're welcome, happy to help.

Unfortunately, from the way you have laid out your report, it is not possible to colour one particular row, for example the school % meeting expected progress column. These values are being shown as aggregated discrete Headers, since they are blue pills on the Rows shelf. You can only apply colours to Marks (i.e. the count of progress measure which you have on the labels shelf). Similarly, you cannot add grand totals to discrete aggregates.

I understand you may have a requirement to produce this report in this exact format, perhaps you are trying to mimic an old Excel report or similar, but if you can try experimenting with how these values can be displayed.

Personally, I should think seeing the KS levels and their %s compared to the national average in a bullet graph would be a nice way to present this data. Then when a user clicks on a level, it drills down into the grades and number of pupils etc.

Good luck!

• ###### 4. Re: Matrix\Grid Calculation Help

Thanks that's what I had thought but I am at the mercy of the management and there advisors who are lost in the past.

Trying to change hearts and minds re: There is a better way to analyse than the Excel way.

I will have a look at the Bullet Graph as it is a good start.

Thanks for getting me this far.

Chris

• ###### 5. Re: Matrix\Grid Calculation Help

Cool. Good luck with that!

By the way, when I say 'that's not possible...' it typically means it's not possible for 99% of Tableau users, including me. Some of the Zen Masters who lurk around may well have a different opinion   Never say never with Tableau!

• ###### 6. Re: Matrix\Grid Calculation Help

Did I hear someone say "Zen Master?"

I'm pretty sure this could be done via one or more multiple axis crosstabs set up in the same layout container on a dashboard, with the grand totals computed via separate calcs in another worksheet positioned in just the right place. The techniques for the multiple axis crosstab are laid out in http://public.tableausoftware.com/views/conditionalformattingv4/Introduction, techniques for doing the grand totals in a separate worksheet in http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-1/.

However, it would take a lot of fiddly formatting work to do this.

Jonathan

• ###### 7. Re: Matrix\Grid Calculation Help

Hi Jonathan

Long time no see.

I go to you help sheet everytime I need to format and use those techniques all the time.  My problem is I do not know how to apply thoase techniques to the row shelf.

I will have a look at the Grand Total Examples they look interesting.

Cheers

Chris

• ###### 8. Re: Matrix\Grid Calculation Help

I have just created the following which puts a Plus or Minus next to the required Filed and I can also filter byt this - Is it possible to Colorise the + or - in the Caluclated field below.  May be a silly question but have to ask it,

IF([Number Achieving Expected Progress]/[Total Number of Pupils]) > ATTR([Sheet1 (ks2-4 National.xlsx)].[Nat % Achieving Expected Progress]) THEN '+' ELSE '-' end

Cheers Chris

• ###### 9. Re: Matrix\Grid Calculation Help

In Tableau v8 you can do this via in a multiple axis crosstab using a couple of calculated fields and a Text mark. In v7 the way I would do this is with a multiple axis crosstab with Shape marks, so the field value is a label and the plus and minus are shapes.

Jonathan

• ###### 10. Re: Matrix\Grid Calculation Help

Jonathan

I have seen a multiple axis crosstab in your example above for V7 so will have a look at that.

I have used that technique in the past and while I found that it looks great with that many fields it was very very slow even with just 250,000 rows and try as I may to speed it up I just couldn't get the performance..

Are there any examples for V8 as I am using that as well.

Thanks

Chris

• ###### 11. Re: Matrix\Grid Calculation Help

V8 should be faster for drawing, but yes, multiple crosstabs are slow. I did a little demo in v8 early on in the beta forum, I haven't seen anything else. I do plan to demo a few more v8 tricks as time goes on.

• ###### 12. Re: Matrix\Grid Calculation Help

Jonathan,

Robin, thanks for all your help.

Chris

• ###### 13. Re: Matrix\Grid Calculation Help

Hi Jonathan

I am going to develop the Workbook above with a Dual Axis to check the speed,  I wll be using your techniques but have a little problem.

So todays silly questions is:-

Using the workbook above as a start I want to use the Level field as a row, the Grade as the columns (these will be individual marks with a Gannt using 0.0 and 1.0 as per your example ) and Count of Progess as the Label.

The problem I have is I do not know how to assign the Grades as the columns with the above technique.

Any tips would really help.  I am comfortable with most of how you put the dual axis together but I am a little stumped with this.

Thanks

Chris

• ###### 14. Re: Matrix\Grid Calculation Help

Hi Chris,

You've set up your workbook with bar marks with no measure, and Tableau is drawing out 6 columns based on the values of the Grade dimension. What I think you'll need to do in the multiple axis crosstab is to have 6 axes, one for each Grade, with 6 calculated fields set up that return the appropriate value for each Grade.

Jonathan

1 2 Previous Next