Overview
Challenge is to report the value of new and partially billed open orders from SAP SD. Data is merged in SAP SD from two different sources, producing a table like the following, where the # rows are the order rows and the ones with billing document numbers are billing rows.
This data shows 25 samples of P-ESH-0028 and 35 samples of P-ESH-0029, both of which have been partially delivered, and both having significant decreases in the billed prices vs. the original net price. This could be due to a price change or the order being late. We want to use the lowest price to calculate the value of the remaining open samples for this order. Some orders won’t have any billing rows yet.
Initial Approach
Initially we can calculate [Remaining Order Value] as follows:
// simplistic, doesn't handle price drops¿
// [Original Order Value]-[Billed Order Value]
¿¿// smarter, checks for un-delivered quantity
¿// SUM([Confirmed qty.]-[Delivery quantity]) * MIN([Net price])¿¿
// best, uses billed price if available¿
SUM([Confirmed qty.]-[Delivery quantity])¿ * IFNULL(MIN([Billing Net Price]), MIN([Net price]))
This works, but requires that we setup our Tableau view to include the billing rows and split by sales order item.
LOD Approach
To gain flexibility in how we can use this calculation, we can create an LOD calculation that pulls in the required dimensions regardless of the view.
// implement via LOD expression so displayed view structure doesn't matter
{ FIXED [Sales document], [Sales Order Item] :
SUM([Confirmed qty.]-[Delivery quantity]) * IFNULL(MIN([Billing Net Price]), MIN([Net price])) }
| |
|---|---|
| Now anywhere we use Sales document, we can get the total remaining value, without the report writer needing to know how to setup the view. | Further, we can now display open order values by any other dimension, like Material. |
|
|
Tableau 10.2 introduces join calculations, allowing string parsing and other operations to be applied to the native database fields to produce matching fields that can be joined. In the following example we want to join a table containing project and sample fields with a table containing those same fields embedded in a workflow name string.
We start by building calculated fields for the secondary data source to parse the project and name from the workflow Name string. Use nested SPLIT commands in this case.
We then can use those debugged calculations directly in the Tableau join window.
Note that this differs from Tableau blending using calculated fields, in that blending only adds columns to a data source, while a join can add rows. In this case we add several rows from WORKFLOW for each project and sample from the PROJECT_SAMPLE_REQUESTS table, each with its own Status. Joins are also much faster than blends.
Although Join Calculations were introduced in Tableau 10.2, they can be converted to Custom SQL and used in older versions of Tableau. In this way Tableau 10.2+ can be used as an SQL code generator.
https://community.tableau.com/thread/247362