Tableau Blog Posts Series – Tips,Tricks,Best Practices
Blog four – Calculation
- NEW: LOD Expression
- Top 15 LOD Expressions https://www.tableau.com/LOD-expressions
- Understanding LOD Expressions http://blog.databender.net/2015/01/22/understanding-level-of-detail-expressions-part-1/ http://blog.databender.net/2015/01/23/understanding-level-of-detail-expressions-part-2/
- Introduction to LOD Expressions http://www.tableau.com/about/blog/2015/2/introduction-level-detail-expressions-36655
- Be careful when creating calculated fields with fields from secondary data sources, especially when replacing data sources. It will not change the data source name in the calculated fields when you replace data sources.
- You can create calculated fields with fields from secondary data sources. But you cannot use fields from secondary data sources when creating set.
- Table calculations- it is important to define compute use (tell Tableau which is first()
Understand compute using in table
- When we set the Compute Using for a table calculation, we are setting the addressing—Compute Using and addressing are synonyms. The addresses—also called “rows in the partition”—are the distinct combinations of values of the dimension(s) used for addressing. Tableau computes a table calculation result for each address.
- All of the dimensions in the view that are not part of the addressing are automatically part of the partitioning. Tableau will restart the computation of the table calculation for each new distinct combination of values of the dimension(s) (and the occasional discrete measure) used for partitioning. As dimensions are added to the view, they are automatically added to the partitioning
http://www.tableausoftware.com/table-calculations
http://drawingwithnumbers.artisart.org/at-the-level-unlocking-the-mystery-part-1-ordinal-calcs/
http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/ - At the level : Each distinct combination of values of the addressing dimension used for At the Level and all addressing dimension(s) above the At the Level dimension increment ordinal table calculations. Addressing dimension(s) below the dimension used for At the Level do not increment ordinal table calculations, and instead return the result of the prior address aka row in the partition for each of those addresses.
- Visualize table calculation http://steineranalytics.com/2014/05/29/visual-way-to-wrangle-tableau-table-calculations/
- Variable size bins http://kb.tableausoftware.com/articles/knowledgebase/creating-variable-sized-bins
- Create bins from calculated fields http://reports4u.co.uk/tableau-create-bins-from-a-measure-calculated-field-pt-2/
- Change dimension to attribute to avoid unnecessary dimension calculations http://www.theinformationlab.co.uk/2013/01/28/5-things-i-wish-i-knew-about-tableau-when-i-started/
- Another to prevent unwanted domain completion is to put all your addressing dimensions on the Level of Detail Shelf, and use discrete measures such as ATTR(myAddressingDimension) to generate the proper headers. For example, put Date on the Detail Shelf and put ATTR(Year) and ATTR(Month) on column/row to be headers.
- Use Lookup and Relative-to for bench marking, index()=1 ( last()=0, first()=0 or index()=n) to hide unwanted , use parameter to choose relative values https://tc13.tableausoftware.com/sites/default/files/materials/Advanced%20Table%20Calculations%20Guide.pdf
- Custom grand total http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-2/
- Custom Subtotals Using Table Calcs – In order to get the subtotals to work at the custom level, you need to add a copy of each dimension to the Level of Detail, then for the calculation set an Advanced… Compute Using that includes all of the copies
- Get the number of rows in the view – WINDOW_MAX(INDEX()), SIZE() at level
- Get total Count Distinct http://kb.tableausoftware.com/articles/knowledgebase/getting-total-count-distinct
- Alternatives for count distinct http://www.tableausoftware.com/about/blog/2013/5/tips-tricks-alternatives-count-distinct-23253
- COUNTD http://kb.tableausoftware.com/articles/knowledgebase/data-blending-with-countd-median-and-rawsqlagg
- Both of the functions, Count and Count Distinct, when searching through the available data ignore all null values and only return the number of fields or unique fields that contain information. However, if the count or countd functions are being used on a dimension and the dimension may contain null values, the function will still return the count of the number of those dimensions regardless.
For Example: if we were counting the number of IDs and those IDs have an additional value attached to them:
1 20
2 11
3 500
4
5 504
The count of IDs will be 5, where the count of values is only 4
If we wanted to go through each row and find all values that meet a condition, then count how many met this condition, we can set up the formulas as such:
Calculation1:
IF Sales >= 500,000
THEN 1
ELSE 0
END
Calculation2:
WINDOW_SUM([Calculation1])
- Dynamic parameter with Data blending (create a calculated field just for blending) http://drawingwithnumbers.artisart.org/creating-a-dynamic-parameter-with-a-tableau-data-blend/
- Calculate first occurrence only http://community.tableausoftware.com/message/223044 This helps me solve the problem of getting the number of upgrade customers per month. Please refer to my question in Tableau community http://community.tableausoftware.com/message/328244#328244
- Compare each against each other (duplicate dimension, previous_value) http://drawingwithnumbers.artisart.org/comparing-each-against-each-other-the-no-sql-cross-product/
- Date Calculation
https://www.interworks.com/blogs/ktreadwell/2012/12/28/date-calculations-tableau
https://www.interworks.com/blogs/dseisun/2012/11/21/using-table-calcs-hide-parts-viz - Top ten calculations http://public.tableausoftware.com/profile/jonathan.drummey#!/vizhome/TheNextNTableCalculations/Introduction
- Date parameter to control multiple dates http://www.theinformationlab.co.uk/2014/09/26/tableau-date-parameters-good/
- Random number generation http://community.tableausoftware.com/docs/DOC-1474
- Decide to use calculated field, table cal, self blend http://redheadedstepdata.io/master-tableau-approach/
- String calculation http://www.clearlyandsimply.com/clearly_and_simply/2014/06/string-calculations-in-tableau.html
- Rank http://vizdiff.blogspot.co.uk/2014/08/revisit-of-rank-functions-in-tableau.html