-
1. Re: Fixed Format Reports - Calculation
Jonathan DrummeyApr 5, 2012 8:23 AM (in response to Ahmed Ahmed)
Hi Ahmed,
If you haven't already, check out the on-demand training at http://www.tableausoftware.com/learn/training. There are a ton of videos there to walk you through learning Tableau. If you like the written word, the Freakalytics book Rapid Graphs with Tableau is also really useful, see http://www.freakalytics.com/.
It seems like the report is a basic crosstab. I've attached a sample workbook. In Tableau, you can easily create calculated fields that add the measures or aggregations of measures together. Then you'll put Measure Names on the Columns shelf and Measure Values on the rows shelf, and select the measures you need, and then do some formatting to get things to display the way you want.
I'm assuming that your datasource already has the initial measures ready for you, if it doesn't then that's a different set of questions.
Jonathan
-
Simple Crosstab.twbx.zip 183.1 KB
-
-
2. Re: Fixed Format Reports - Calculation
Ahmed Ahmed Apr 5, 2012 8:47 AM (in response to Jonathan Drummey)Hi Jonathan,
As I mentioned in the post that its a straight table and in the example, the Assets, Current Assets & Fixed Assets are the few headings that I have to include in my report. These headings and the values are not available in the source. I have to introduce these headings and have also perform the vertical summations.
First milestone is to add the missing headings in the straight table and second one is to calculate the vertical summation of data based on the heading e.g. to calculate the Fixed Assets, I have sum the value of Cash & Receivables.
I did check all the videos but thanx for the book.
I hope that understand the issue well.
Ahmed.
-
3. Re: Fixed Format Reports - Calculation
Jonathan DrummeyApr 5, 2012 9:37 AM (in response to Ahmed Ahmed)
I'm sorry, I don't think I'm understanding exactly what you want. Are you needing the headings to be text or the values of the measures (or summations of measures)? If you created a mock-up document or spreadsheet, I'm pretty sure something could be figured out.
FYI, Assets, Current Assets, and Fixed Assets measure values can be calculated fields and don't require the measures they are based on to be in the view. You can see this in the worksheet I provided, if you drag one of the fields off of Measure Values the rest of the calculated fields stay the same. By default, Tableau makes the heading name the same as the name of the measure, however when using Measure Names/Measure Values you can override that by right-clicking on the header label and selecting Edit Alias...
Jonathan
-
4. Re: Fixed Format Reports - Calculation
Ahmed Ahmed Apr 5, 2012 9:56 AM (in response to Jonathan Drummey)Hi,
Seems, probably I am not able make others understand the issue. Let me try one more time.
The data source doesn't know what Asssts, Fixed Assets & Current Assets are. These terms are aliens terms for the prospect's data source. My task is two fold:
1) Introduce the above mentioned terms in the report
2) To calculate the values against each terminology.
I am sorry, dont have a mock sheet.
Ahmed.
-
5. Re: Fixed Format Reports - Calculation
Ahmed Ahmed Apr 5, 2012 10:13 AM (in response to Ahmed Ahmed)Hi,
I have attached the excel sheet here. The understanding is the Code that starts with 101 are Current Assets whereas that starts with 102 are Fixed Assets.
We are not allowed to add an external sheet or table that will have the information about what Assets, Current Assets & Fixed Assets are. The only thing that we know is Code values with prefix 101 are Current Assets and prefix 102 are Fixed Assets.
Based on this information, I have to show on the report Current Assets, Fixed Assets and Assets.
I hope this will make the case clear.
Ahmed
-
DB.xls 23.5 KB
-
-
6. Re: Fixed Format Reports - Calculation
Jonathan DrummeyApr 5, 2012 10:27 AM (in response to Ahmed Ahmed)
Hi Ahmed,
Since you've listed the order of operations twice now as 1. "create the header" and 2. "calculate the values", I think you might not be clear about how Tableau works, which is kind of the opposite.
Tableau starts with a dataset, which it flattens into a tabular format with columns being dimensions and measures, and rows being values. Tableau also allows you to create new dimensions and measures on the fly by creating calculated fields. Then you drag your dimensions and measures (whether original or calculated) into the view and Tableau automatically generates header and data columns and rows based on the combination of those.
I assume that your dataset has columns like Cash, Receivables, Plant, and Machinery. Therefore, you can create new calculated fields like "Current Assets" that would have as it's function [Cash]+[Receivables]. I did that in the workbook I attached above for the "Margin + Profit" measure, which is [Margin]+[Profit]. Calculated fields can be based on other calculated fields, so "Assets" could be [Fixed Assets] + [Current Assets].
When you want to show multiple measures at once in a crosstab, you can drag Measure Names (a convenience variable) to the Columns shelf, then put Measure Values (another convenience variable) on the Text shelf. This creates a new Measure Values card that lists all the measures that will be displayed on it, and you can drag and drop those measures off it. As you do so, the fields will be removed from the view, but the calculations will not change.
Does that make sense? And, more importantly, does that meet your needs?
Jonathan
--
You were adding the sample data when I as making this post, and now I understand your comment about needing to make headers, I'm working on an update.
Message was edited by: Jonathan Drummey
-
7. Re: Fixed Format Reports - Calculation
Jonathan DrummeyApr 5, 2012 10:50 AM (in response to Ahmed Ahmed)
Hi Ahmed,
Here's an updated version. Seeing your data helped make the issues you were experiencing make sense. The data you supplied is "tall" and we need to make it "wide" by adding not just the headers for the Assets fields, but also the other columns. Tableau has a special way of generating crosstabs that isn't as easy as just dragging a dimension like the Description from the Master table and then tacking on a few more.
Therefore, I created a calculated field for each column in the desired output. To generate the Fixed and Current Assets columns, I did a string comparison, here's the one for Current Assets: IF LEFT(STR([Code]),3) = "101" THEN [Amt] END. The Assets column needed to be SUM([Current Assets])+SUM([Fixed Assets]), I'm not sure why Tableau didn't want to just add Current & Fixed together, apparently Tableau is a bit limited in how much it nests adding row-level calculations as opposed to aggregate calculations (which I do know can have many levels of nesting).
Let me know if this works for you,
Jonathan
PS: If you needed more than a dozen or so headers, then I'd probably approach this differently and use Custom SQL to do a UNION of a self-join(s) on the data, with the original data returned as one query and then additional queries to return the necessary aggregates as additional rows. This would be more efficient in Tableau than creating too many calculated fields.
-
Simple Crosstab v2.twbx.zip 354.8 KB
-