mtb5_Nested Calculations_lookup(hide data)filter_address(direction)_partition(scope)_Rank_Size_Index

I would like to draw your attention to the work of three individuals who helped to make this chapter possible. Joshua Milligan provided the idea of directional and non-directional as a taxonomy[tækˈsɑːnəmi]分类学  for considering how Tableau table calculation functions are applied. Of the dozens of blogs, forum posts, conference sessions, articles, and white papers reviewed for this chapter, Jonathan Drummy's blog post ( http://drawingwithnumbers.artisart.org/at-the-level-unlocking-the-mystery-part-1-ordinal-calcs/ ), At the Level – Unlocking the Mystery Part 1: Ordinal Calcs, was the clearest and most insightful for understanding the various nuances[ˈnuːɑːns]细微差别  of partitioning and addressing. Lastly, Joe Mako's unsurpassed understanding of the inner workings of Tableau and his willingness to share that insight through the Tableau forums was very helpful.

In this chapter, we will discuss the following topics:

• • A definition and two questions
• • Introducing functions
• • Directional and non-directional table calculations
• • Application of functions

     Let's go into more detail and start this chapter off with a general definition of table calculations and two resulting questions, which we will answer by the end of this chapter.

A definition and two questions

     As discussed in https://blog.youkuaiyun.com/Linli522362242/article/details/124207205, Getting Up to Speed – A Review of the Basics, calculated fields can be categorized as either row-level, aggregate-level, or table-level. For row - and aggregate-level calculations, the underlying data source engine does most (if not all) of the computational work and Tableau merely visualizes the results. For table calculations, Tableau also relies on the underlying data source engine and the
available RAM on your machine to execute computational tasks; however, after that work is completed and a dataset is returned, Tableau performs additional processing before rendering the results.

Let us look at the definition of table calculations, as follows:

     A table calculation is a function performed on a cached dataset that has been generated as a result of a query from Tableau to the data source.

     Let's consider a couple of points regarding the dataset in cache mentioned in the preceding definition: 

• This cache is not simply the returned results of a query. Tableau may adjust the returned results. We will consider this in Chapter 6, All About Data – Data Densification, Cubes, and Big Data; Tableau may expand the cache through data densification but usually, it is user-driven.
• It's important to consider how the cache is structured. Basically, the dataset in cache is the table used in your worksheet and, like all tables, is made up of rows and columns. This is particularly important for table calculations since a table calculation may be computed as it moves along the cache. Such a table calculation is directional (for an example, see Figure 5.1)
• Alternatively, a table calculation may be computed based on the entire cache with no directional consideration. These table calculations are non-directional. Directional and non-directional table calculations will be explored more fully in the Directional and non-directional table calculations section.

     Note that in the Tableau documentation, the dataset in cache is typically referred to as a partition缓存中的数据集通常称为分区. This chapter will often use both terms side by side for clarity.

     The structure of this chapter was created with the intent of providing a simple schema for understanding table calculations. This is communicated through two questions:

• • What is the function?
• • How is the function applied?

     These two questions are inexorably[ɪnˈeksərəbli] connected密不可分的. You cannot reliably apply something until you know what it is. And you cannot get useful results from something until you correctly apply it. The Introducing functions section explores each unique table calculation function, and how each can be considered directional or non-directional. The Application of functions section explores how table calculations are applied to the view via partitioning and addressing dimensions.

Introducing functions

     As discussed earlier in this chapter, it may be helpful to enhance your understanding of table calculations with the following two questions: what is the function and how is the function applied? We will begin by grouping each table calculation function to be directional or non-directional.

     Tableau offers a wide range of table calculations, but if we narrow our consideration to unique groups of table calculation functions, we will discover that there are only 11.

The following table shows those 11 functions organized into two categories:

     As mentioned in A definition and two questions, non-directional table calculation functions operate on the entire cache and thus are not computed based on movement through the cache. For example,

• the SIZE function doesn't change based on the value of a previous row in the cache and is therefore considered non-directional.
  
       Returns the number of rows in the partition. For example, the view below shows quarterly sales. Within the Date partition, there are 7 rows so the Size() of the Date partition is 7.
  
• On the other hand, RUNNING_SUM does change based on previous rows in the cache and is therefore considered directional.
  
       Returns the running sum of the given expression, from the first row in the partition to the current row. (table down)

     In the following section, we'll see directional and non-directional table calculation functions in action.

Directional and non-directional table calculations 

Let us have a closer look at directional and non-directional table calculations: 

• 2. Navigate to the Directional/Non-Directional worksheet. 
• 3. Create the calculated fields, as shown in the following table:
  • Lookup : Notice the -1 included in this calculation. This instructs Tableau to retrieve the value from the previous row.

    LOOKUP( SUM([Sales]), -1 )

    
    LOOKUP(expression, [offset]) :
         Returns the value of the expression in a target row, specified as a relative offset from the current row. Use FIRST() + n and LAST() - n as part of your offset definition for a target relative to the first/last rows in the partition. If offset is omitted, the row to compare to can be set on the field menu. This function returns NULL if the target row cannot be determined.
    
         The view below shows quarterly sales. When LOOKUP (SUM(Sales), 2) is computed within the Date partition, each row shows the sales value from 2 quarters into the future.
    
  • Size : The SIZE function returns the number of rows in the partition. Therefore, as can be seen in the following screenshot, the size equals the total number of rows

    SIZE()

    
  • Window Sum : The Window sum functions (WINDOW_SUM(expression, [start, end])
    can operate either directionally or non-directionally.
         Since this example does not include the [start, end] option, it operates non-directionally

    WINDOW_SUM( SUM([Sales]) )

    
    WINDOW_SUM(expression, [start, end])
         Returns the sum of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
         For example, the view below shows quarterly sales. A window sum computed within the Date partition returns the summation of sales across all quarters.
    
         Example : 
              WINDOW_SUM(SUM([Profit]), FIRST()+1, 0) computes the sum of SUM(Profit) from the second row to the current row.
  • Window Sum w/ Start&End : 

    WINDOW_SUM( SUM([Sales]), 0, 1 )

    computes the sum of SUM(Sales) from the current row to the next row.
  • Running Sum :

    RUNNING_SUM( SUM([Sales]) )

    
    RUNNING_SUM(expression) : Returns the running sum of the given expression, from the first row in the partition to the current row. （Accumulation operation） 

•  4. Place Category and Ship Mode on the Rows shelf.

•  5. Double-click on Sales, Lookup, Size, Window Sum, Window Sum w/Start&End, and Running Sum to populate the view as shown in the following screenshot:

• 6. The table in Figure 5.1 shows you that the Size function and the Window Sum function are the only two non-directional functions. All others present a number depending on the direction. For example, Lookup moves down and prints the value prior to the actual field. You can change the direction by clicking on the triangle of any table calculation field and selecting Edit Table Calculation.

     In answer to the question what is the function, we have considered a taxonomy composed of two categories: directional and non-directional. Now, we will move on to considering the table calculation functions individually. Regretfully, space does not allow us to explore all table calculations; however, to gain a working understanding, it should suffice to consider all but one of the unique groups of options. The four table calculations that begin with Script_ will be covered in Chapter 15, Programming Tool Integration. 

     Although some effort has been made to make the following exercises useful for real-world scenarios, each is focused on demonstrating table calculation functionality while considering how each interacts directionally or non-directionally with the dataset in cache (also known as the partition).

Exploring each unique table calculation function

The following exercises will show us each table calculation individually, based on an example.

Lookup(hide data) and Total

     The objectives of the following worksheet are to display those customers who made purchases in the last 2 days of 2013, and the associated absolute as well as relative sales of the month of December(Purchases per customer in the last 2 days as a percentage of total sales in the last month of 2013).

• 1. In the workbook associated with this chapter, navigate to the Lookup/Total worksheet.
• 2. Drag Customer Name and Order Date to the Rows shelf. Set Order Date to Month/Day/Year(date value truncated) discrete by right-clicking and selecting Day as well as Discrete.
• 3. Place Order Date on the Filters shelf, choose to view only December 2013, and select Month/Year as the date format.
  
  The filter on Order Date ensures that the dataset returned to Tableau only includes data from the month of December 2013.
  
  Objectives: only show those Customers' Name who made purchases in the December 2013
• 4. Create the following calculated fields:
  • Objectives: Customers who made purchases in the last 2 days of 2013
    
    Max(): Returns current customer's last purchase date
    
    Lookup Intervening Time :will return an integer that reflects the difference between the date returned by the LOOKUP function and 12/31/2013 (12/31/2013 - date returned). Note that the LOOKUP function has an offset of 0(=current row). This results in each row returning the date associated with that row. This differs from directional and non-directional table calculations, which include a LOOKUP function with an offset of -1, which caused each row in the view to return data associated with the previous row.

    DATEDIFF( 'day', LOOKUP( Max([Order Date]), 0),
                     MAKEDATE(2013, 12,31)
            )

    MAKEDATE(year, month, day) : 
         Returns a date value constructed from the specified year, month, and date.
         MAKEDATE(2013, 12, 31) = #December 31, 2013#
    
    6. Place Lookup Intervening Time on the Filters shelf and choose a range from 0 to 1.( 0<= 12/31/2013 - date returned <=1 )
  • % Total Sales :

    SUM([Sales])/TOTAL( SUM([Sales]) )

      the TOTAL( SUM([Sales]) ) calculated field returns the total sales for the entire dataset. Dividing SUM([Sales]) by this total returns the percentage of the total.

• 5. Right-click on % Total Sales and select Default Properties | Number format... to set the number format to percentage with two decimal places.

• 7. Double-click on Sales and % Total Sales to place both fields on the view.
• 8. Format as desired:Purchases per customer in the last 2 days as a percentage of total sales in the last month of 2013 (First filter ( TOTAL( SUM([Sales]) ) )==>Second filter (Lookup SUM([Sales]) in the last 2 days) 
  
  Think of Lookup Intervening Time as not filtering but hiding all but the last two days in December. This hiding ensures that the data necessary to calculate % Total Sales is in the dataset in cache/partition.

     At first glance, you might think that you could simplify this workbook by removing Lookup Intervening Time from the Filters shelf and adjusting the filter on [Order Date] to display only the last two days of December. However, if you do this, % Total Sales will add up to 100% across all rows in the view(Last 2 days of purchases per customer as a percentage of last 2 days of total sales in the last month of 2013), which would not satisfy the workbook's objectives(Purchases per customer in the last 2 days as a percentage of total sales in the last month of 2013).​​​​​​​

Previous Value

     The objectives of the following worksheet are to return the aggregate value of sales for each year and set next year's sales goal. Note that two options have been provided for determining next year's sales goal in order to demonstrate how PREVIOUS_VALUE differs from LOOKUP . Also note that PREVIOUS_VALUE behaves directionally. 

Let us have a look at the steps:

• 1. In the workbook associated with this chapter, navigate to the Previous Value worksheet.
• 2. Create the following calculated fields:
  • Next Year Goal Prv_Val :

    PREVIOUS_VALUE( SUM([Sales]) ) * 1.05

    PREVIOU