In this session, we’ll create the last Data Rule we need to calculate our On-Sale Discounts. We’ll begin by outlining the design of the calculation, then we’ll implement that design to build the rule in Kepion, and lastly, we’ll map the Deflator % Accounts to their related P&L Accounts.
This script maps the [Deflator % AccountID] to the [P&L AccountID]. It does this by checking the AccountID in each row, and attempting to match that ID to one of the 8 Deflator % IDs. If any of those IDs are matched, it replaces it with the associated P&L Account IDs.
WHEN A.[AccountID] = 24 THEN 5
WHEN A.[AccountID] = 25 THEN 7
WHEN A.[AccountID] = 26 THEN 8
WHEN A.[AccountID] = 27 THEN 9
WHEN A.[AccountID] = 28 THEN 17
WHEN A.[AccountID] = 29 THEN 18
WHEN A.[AccountID] = 30 THEN 19
WHEN A.[AccountID] = 31 THEN 20
In this session, we’ll create the last Data Rule we need to calculate our On-Sale Discounts.
We’ll begin by outlining the design of the calculation, then we’ll implement that design to build the rule in Kepion, and lastly, we’ll map the Deflator % Accounts to their related P&L Accounts.
For this calculation, we need to multiply Gross Sales Before Returns with each of these 8 Deflator % Accounts. While we could use the method from the previous calculation and pivot all 8 of the accounts and then define 8 individual calculations, this would be a bit tedious.
In addition, the data from these two sources doesn’t quite line up, as the Deflator % values were entered at the Brand level and the Gross Sales Before Returns is at the SKU.
To solve both of these issues, let’s utilize the Join Component. In SQL a Join is an operation that combines rows from two or more tables, based on a related column. Joining these tables together will naturally line up the calculations for us, without the need to pivot.
In addition, we can join both of these tables with the Brand attribute in order to distribute the Deflator % values to the SKU level. As both Brands and SKUs have an associated Brand attribute, we can use this column to map the ProductIds together.
Let’s start by adding a new Data Rule. In the All Data Rules tab select Add and name it Calculate On-Sale Discounts. To keep things simple, we’ll create it as a VIEW Rule.
In the Rule editor we’ll need to select two Data Sources: The Deflator Percent – Writeback Partition, and the Reporting Model – Calculated Partition. For the calculated data, we can scope it down to just the Account: Gross Sales Before Returns, as we don’t need the other values in our calculation.
Let’s also select Dimension from the Data Source and choose Product. This brings the Product Dimension Table into the calculation. We need this to get the Brand Attribute values. Only the MemberId is brought in by default, but we can choose to include additional columns from the table. Let’s select Brand.
Now select a Join Component. First, let’s join the Revenue Data with the Brand attribute. So, for the source, select Reporting – Calculated and for the Join select the Product Dimension table. Kepion will automatically try to find shared columns to join the data along.
Now that these two tables have been joined together, in the column tab, we can include the Brand information. Now let’s create a second join and add this same column to the Deflator % data.
Now let’s join both these tables together. As these tables have the exact same structure, every column is included as a potential piece of the Join condition.
However, we already know that the Product values between these tables don’t line up, so let’s remove that from the Join. We also know that these tables use a different set of Accounts, so we can remove that as well.
In the Column tab, the first table in the Join condition is specified as Table A, while the second table is B. We want our calculation at the SKU level, so let’s first remove all the Brand information. Since Table A is our Deflator % data, A.ProductID is the Brand information. Let’s replace this with B.ProductID. Let’s also include the values from both tables.
I’m going to rename the value columns for clarity.
In the Preview tab, we now have one combined table of data, at the SKU level, with both values. In addition, the Revenue and Deflator % values are already lined up for us. All we need to do is multiply these values together.
In the column tab, add another column, and define it as the Reporting value times the Deflator % value. Then let’s remove the individual value columns.
Everything is looking good, but there’s one additional detail to address. The accounts listed here are the Deflator % Statistical accounts, however, we need the values to flow into the related P&L Accounts. Again, we could pivot these members into their own columns, rename them, and then unpivot, but there’s a quicker solution here.
Let’s configure the column definition for AccountID and choose ‘Custom Member Expression’. This option lets us write a SQL expression that will attempt to resolve to a Member within the selected Dimension. I prepared a simple statement beforehand that maps the statistical account ID to the P&L AccountID.
This script, and a quick explanation of how it works, are provided below the video. Now all the data is flowing into the correct accounts. Let’s publish it. Then save and deploy the application.
If we navigate to the Reporting Model, we can see that the P&L Report is now nearly complete.
In this session, we outlined the design of the calculation, and then implemented that design in Kepion, and lastly, we used SQL to map the Deflator % Accounts to their related P&L Accounts.
In the last video of Part IV, we’ll use MDX to calculate the Gross Margin %