In the example below, we will make use of the following components and methods:
• Embedded Table
• Joined Table
• Concatenated Table
• Group and aggregate options
• Expression fields
Let us assume we have a Worksheet table called 'Revenue'. The 'Revenue' table includes the fields 'Order Num' and 'Product Total', listing the total value of each order. Follow the steps below to analyze this data and detect the proper 'price break' threshold for classifying orders as “large” or “small”:
1. Create a new Embedded Table with one data cell, and name this table 'PriceBreak'. It will contain the 'price break' value that we experiment with. In the table cell, enter 40000 as the first guess for the price break value. This indicates that all orders with a total of $40,000 qualify as “large orders”, and all orders with a total equal to or less than $40,000 qualify as “small orders”.
Next, we will calculate the total sales of all of the large or 'High End' orders, and the total sales of all the small or 'Low End' orders, both before and after the discounts/increases are taken into account. We do this in order to compare the sales revenue before and after the discount model is put into effect.
2. In the 'Revenue' table, group the data by 'Order Num' and summarize by 'Product Total' so that we have the total sales amount for each order.
The data in the 'Revenue' table can now be split into two separate tables, 'HighEnd' and 'LowEnd'.
3. To obtain the 'HighEnd' table, specify a join between the 'Product Total' column and the 'PriceBreak' table, and set the join type to: Revenue.Total >= PriceBreak.PriceBreak
4. To obtain the 'LowEnd' table, specify a join between 'Product Total' and 'PriceBreak' and set the join type to: Revenue.Total < PriceBreak.PriceBreak
5. For the 'HighEnd' table, create an expression field named 'Discount' to calculate the new order total with the 5% discount applied. The SQL formula for the 'Discount' expression field should be set to
field['Total']*0.95