# Tutorial 4 - Basic Business Rules

## 1. Getting Started

In tutorial 3, we've introduced the basics of querying data using CubiQL expressions. We also learned how to configure several Modeler features using CubiQL expressions. In this tutorial, we learn how to model more complex business logic, using CubiQL expressions.

Before you get started with this tutorial, make sure that you restore the application to the base version of this tutorial, by running the following commands:

### Run Me

```\$ ./tools/restore-base tutorial-4
\$ make clean
\$ lb config
\$ make```

### Tip

Note that your answers from the previous exercises will be overwritten. If you would like to keep them for later reference, make sure that you make a local copy, before running the script

### 1.1. Using Basic Arithmetic Operators

In this tutorial, we learn to use some basic arithmetic operators to compute the values of measures. But before we dive into installing business rules, we're going to do some simple math in the REPL to see how predefined arithmetic operators in the measure service can be used in CubiQL.

### Run Me

`\$ lb measure-service repl --uri ws://localhost:55183/modeler-training/websocket/measure`

Let's add two numbers using the `+` operator.

```> 1 + 1

value of int
┈┈┈┈┈┈┈┈┈┈┈┈
2

Displayed all 1 result entries.```

Using the `+` operator is actually a shorthand notation for using the predefined `add` operator:

```> add(1,1)

value of int
┈┈┈┈┈┈┈┈┈┈┈┈
2

Displayed all 1 result entries.```

CubiQL supports the following arithmetic and rounding operators:

Operator Description Supported argument types
`add(x,y)` or `x + y` Adds `y` to `x`. `int`, `int128`, `decimal`, `float`, and `string`
`subtract(x,y)` or `x - y` Subtracts `y` from `x` `int`, `int128`, `decimal`, and `float`
`multiply(x,y)` or `x * y` Multiplies `x` by `y` `int`, `int128`, `decimal`, and `float`
`divide(x,y)` or `x / y` Divides `x` by `y` `int`, `int128`, `decimal`, and `float`
`negate(x,y)` or `- x` Returns `-x` `int`, `int128`, `decimal`, and `float`
`abs(x)` Calculates the absolute value `x` `int`, `int128`, `decimal`, and `float`
`sqrt(x)` Calculates the non-negative square root of `x`, for non-negative `x` `float`
`pow(x,y)` Computes `x` raised to the power `y` `float`
`mod(x,y)` computes the remainder of dividing `x` by `y` `int` and `int128`
`log(x)` Computes the natural logarithm of `x` `float`
`floor(x)` Rounds the decimal value `x` to the largest integral value not greater than `x` `decimal` and `float`
`ceil(x)` Rounds the decimal value `x` to the smallest integral value not smaller than `x` `decimal` and `float`
`minimum(x,y)` Returns the smaller of `x` or `y` `int`, `int128`, `decimal`, `float`, and `string`
`maximum(x,y)` Returns the larger of `x` or `y` `int`, `int128`, `decimal`, `float`, and `string`

Go ahead and do some calculations in the CubiQL REPL to see how these operators work!

### Run Me

For instance, you could try to calculate the percentage increase between two numbers:

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
old = 200d,
new = 220d,
increase = new - old
) in (
increase / old * 100d
)
// Exiting multi-line mode, now evaluating.

value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
10.0d

Displayed all 1 result entries.```

The binding precedence of arithmetic operators in CubiQL expressions is exactly what you would expect: the operators `*` and `/` bind first, and `+` and `-` bind next. You can use parenthesis `(...)` to enforce different precedence.

### Run Me

Let's try to write the same calculation of the percentage increase without using a `let`. You'll need to use parentheses to calculate the increase (subtraction) before dividing it by the old value.

```> (220d - 200d) / 200d * 100d

value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
10.0d

Displayed all 1 result entries.```

Note that the arguments of an arithmetic operator must all be of the same type (e.g. `int`, `decimal`).

### Run Me

Let's try to add an integer and a decimal. As expected, this will raise an error message:

```> 1.5d + 1
Problems during preprocessing in REPL:
[ERROR] (row 0 and column 0 to row 0 and column 8) Operator add is not defined on arguments (decimal,int) in any of its defining predicates: int:add, string:add, float:add, int128:add, decimal:add.```

To solve this error, make sure to use the correct number notation when you're using a literal:

```> 1.5d + 1.0d

value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
2.5d

Displayed all 1 result entries.```

When an argument with an incompatible type originates from a measure or some CubiQL expression, you should explicitly convert the arguments into the type of the other (e.g. `int` to `decimal`, or `decimal` to `int`). Since converting a decimal number to an integer would lose precision, we want to convert the integer value into to decimals instead. We can do this using the cast expression `as decimal`:

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
expr = 1
) in (
1.5d + expr as decimal
)
// Exiting multi-line mode, now evaluating.

value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
2.5d

Displayed all 1 result entries.```

Some of CubiQL's built-in operators support less argument types than you might expect. For instance, `sqrt(x)` only accepts arguments of type `float`. There might be times where you want to apply this operator to a `decimal`, `int`, or `int128` value. In this case, you can also use a cast expression to convert the value into a `float` before passing it to the operator.

### Run Me

Let's try to calculate the square root of an `decimal` type value. As expected, this will raise an error message:

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
expr = 4d
) in (
sqrt(expr)
)
// Exiting multi-line mode, now evaluating.
Problems during preprocessing in REPL:
[ERROR] Operator sqrt is given arguments (decimal), but it only supports these overloads:
at arguments (float), returning float.```

Now, use a cast expression to convert the value to a `float`:

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
expr = 4d
) in (
sqrt(expr as float)
)
// Exiting multi-line mode, now evaluating.

value of float
┈┈┈┈┈┈┈┈┈┈┈┈┈┈
2.0f

Displayed all 1 result entries.```

### 1.2. Using String Operators

Up until now, we've only applied operators to numeric values. As you might have noticed in the table at the beginning of this lesson, it's also possible to apply operators `add(x,y)`, `minimum(x,y)`, and `maximum(x,y)` on arguments of type `string`. The following table gives an overview of CubiQL's built-in `string` operators:

Operator Description
`add(x,y)` or `x + y` Concatenates `x` to `y`.
`minimum(x,y)` Returns the smaller of the first unmatching character in strings `x` and `y`
`maximum(x,y)` Returns the larger of the first unmatching character in strings `x` and `y`
`to_upper(x)` Converts the characters of the string `x` to upper-case
`to_lower(x)` Converts the characters of the string `x` to lower-case
`length(x)` Returns the number of (Unicode) characters in string `x`

### Run Me

Let's see the result of applying `add(x,y)` on `string` typed arguments:

```> add("Cubi","QL")

value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"CubiQL"

Displayed all 1 result entries.

> "Cubi" + "QL"

value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"CubiQL"

Displayed all 1 result entries.```

As you can see, when you apply this operator on two `string` typed argumemts, it acts as a concatenation operator.

Let's also try some of the other operators:

```> to_lower("CubiQL")

value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"cubiql"

Displayed all 1 result entries.

> length("CubiQL")

value of int
┈┈┈┈┈┈┈┈┈┈┈┈
6

Displayed all 1 result entries.

> minimum("aa","ab")

value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"aa"

Displayed all 1 result entries.```

## 2. Calculating Measure Values

Okay, we're ready to start computing the values of measures in our training application. We have extended `<TRAINING_HOME>/src/config/Measures.csv` with the two additional measures for this purpose:

```SkuMarkdown_R_TY_SK,Markdown \$ TY,Sku,decimal,0d,average,replicate,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""\$"",""thousandSeparated"":true,""mantissa"":2}",right,true,,DerivedAndStored,,
SkuSalesPrice_R_TY_SK,Sales Price TY,Sku,decimal,0d,average,replicate,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""\$"",""thousandSeparated"":true,""mantissa"":2}",right,true,,DerivedAndStored,,```

These measures both follow the naming guidelines in the section called “Naming Conventions”. They are relatively straight-forward:

• `SkuMarkdown_R_TY_SK` holds the markdown value in \$ amount. We learn in this tutorial how this measure can be computed from `SkuMarkdownPct_P_TY_SK`, the markdown in % which is already defined.
• Consequently, the markdown in \$ will be used to compute the actual sales price for a product, `SkuSalesPrice_R_TY_SK`.
• Both of these measures are read-only.
• We specify the derivation type to be `DerivedAndStored` since data for this measure will be calculated with a business rule.

We have also added these measures to the SKU Maintenance view. Since we haven't added business rules for these measures yet, their values are still equal to the default value of the measure (`0d`).

To specify how the above two new measures should be calculated, we use CubiQL expressions.

### 2.1. Computing Markdown \$ (TY)

Let's compute `Markdown \$ TY` for a SKU (`SkuMarkdown_R_TY_SK`). We would like the `Markdown \$` to be the multiplication of the original `Retail` (`SkuRetail_R_TY_SK`) and the `Markdown %` (`SkuMarkdownPct_P_TY_SK`).

### Run Me

Use the CubiQL REPL to look at the results of this calculation:

```> SkuRetail_R_TY_SK * SkuMarkdownPct_P_TY_SK

Product : Sku ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Granny Smith" ┊             0.0d
"Beets" ┊             0.0d
"Honeyscrisp Apples" ┊          1.2225d
"Clementines" ┊           2.697d
"Blood Orange" ┊           0.129d
"Pink Grapefruit" ┊             0.0d
"Navel Oranges" ┊             0.0d
"Radishes" ┊             0.0d
"Ginger" ┊             0.0d

Displayed all 9 result entries.```

The following steps guide you through the process of configuring this CubiQL expression as the calculation for `SkuMarkdown_R_TY_SK` in your application.

### Modify Me

Let's create a new file, `<TRAINING_HOME>/src/rules/tutorial4.logic` and place the following code into that file:

```^modeler_config:metamodel:cubiql:metric_expr["SkuMarkdown_R_TY_SK"] = """
SkuRetail_R_TY_SK * SkuMarkdownPct_P_TY_SK
""".```

In our training application, we update the `modeler_config:metamodel:cubiql:metric_expr` predicate to associate the CubiQL expression `SkuRetail_R_TY_SK * SkuMarkdownPct_P_TY_SK` to measure `SkuMarkdown_R_TY_SK`. The logic in the `modeler_config:metamodel:cubiql` block takes care of installing the rule in the measure model when this predicate is being updated.

### Build Me

As we have only made changes to CubiQL expressions, we do not need to rebuild the whole application, but can instead use the following commands:

```\$ make load-cubiql-rules
\$ make load-test-data```

With the first command, we load the business rules and then the second command is used to re-import the test data (as the first command reverts the application to a state where no data is available in the application, yet).

Now that we have defined this rule, we should be able to verify that the on SKU Maintenance view, the value of `Markdown \$ TY` is exactly the retail price multiplied by the `Markdown %`. Furthermore, you can validate that `Markdown \$` correctly recalculates each time you make a change to either `Retail` or `Markdown %`.

### Note

In our training application, all CubiQL rules are expected to be stored in LogiQL files with a `.logic` suffix under the directory `<TRAINING_HOME>/src/rules`. You can store multiple rules in one file. Rules that are conceptually related are often organized into one file. For instance, all rules that relate to the computation of inventory may be grouped in `Inventory.logic`, whereas all rules that compute various metrics of sales may be grouped in `Sales.logic`. In this tutorial, we'll simply keep all of the rules in one file called `tutorial4.logic`.

### 2.2. Computing Sales Price (TY)

Let's now also add a rule that calculates the ```Sales Price``` of a SKU (`SkuSalesPrice_R_TY_SK`) by subtracting the `Markdown \$` (`SkuMarkdown_R_TY_SK`) from the `Retail` price of a SKU (`SkuRetail_R_TY_SK`). Can you try writing this rule yourself in `<TRAINING_HOME>/src/rules/tutorial4.logic`?

### Modify Me

If you need some help, go ahead and add the following rule to `<TRAINING_HOME>/src/rules/tutorial4.logic`:

```^modeler_config:metamodel:cubiql:metric_expr["SkuSalesPrice_R_TY_SK"] = """
SkuRetail_R_TY_SK - SkuMarkdown_R_TY_SK
""".```

### Build Me

Run the commands below:

```\$ make load-cubiql-rules
\$ make load-test-data]```

Check out the effect of these rules using the SKU Maintenance view: make sure that the sales price is indeed the retail minus the markdown.

### 2.3. Computing Cost of Goods Sold (TY)

Let's take a look at the TY Data view, which is pre-configured to display some measures. You probably have noticed that some measures have no data (displaying the default value `0`), such as `Cost of Goods Sold TY`.

Let's see more arithmetic operators in action by adding the rule to calculate ```Cost of Goods Sold TY```.

We can calculate the `Cost of Goods Sold` (`COGS_R_TY_SKSTMO`) by multiplying the ```Sales \$``` (`SalesRetail_R_TY_SKSTMO`) by 1 - Margin % (`MarginPct_P_TY_SKSTMO`). Give it a try and see if you can construct such a rule.

### Modify Me

If you need a bit of help, the following rule should be added to `<TRAINING_HOME>/src/rules/tutorial4.logic` to define `Cost of Goods Sold TY`:

```^modeler_config:metamodel:cubiql:metric_expr["COGS_R_TY_SKSTMO"] = """
SalesRetail_R_TY_SKSTMO * (1d - MarginPct_P_TY_SKSTMO)
""".```

Now let's take a closer look at what our rule says:

1. The formula is pretty straight forward and similar to the ones that you wrote before. It contains two operators (multiplication and subtraction).
2. As the multiplication operator has precedence over the subtraction operator, we enforce different precedence (i.e. subtraction first) using parenthesis.
3. Notice the `d` suffix for the number `1`? This suffix is needed, as arithmetic operators only apply to numeric values of the same type. Without the suffix `d`, `1` is interpreted as an integer which is a different type than `MarginPct_P_TY_SKSTMO`, a decimal. We, therefore, indicate that we want the decimal number 1, using `1d`.

### Build Me

Run the commands below to load the new rule:

```  \$ make load-cubiql-rules
\$ make load-test-data```

Take a look at the `TY Data` view now. Do you see data for `Cost of Goods Sold TY`? No? That's correct as if you take a close look, we are not yet calculating the `SalesRetail_R_TY_SKSTMO` measure, the values are therefore always `0` (which is the default value for this measure). Let's add a rule to calculate the `Sales \$ TY` and then go back and validate our measure rule for `COGS_R_TY_SKSTMO`.

### 2.4. Computing Sales \$ (TY)

Before we can validate the calculation for `COGS_R_TY_SKSTMO`, we need to calculate `Sales \$ TY`. Let's now add a rule to calculate this measure.

The formula that we need to use is very simple: `Sales \$` (`SalesRetail_R_TY_SKSTMO`) is the `Sales Units` (`SalesUnits_U_TY_SKSTMO`) multiplied by the `Average Selling Price` (`AvgSellingPrc_R_TY_SKSTMO`).

### Run Me

Use the CubiQL REPL to look at the results of this multiplication:

```> SalesUnits_U_TY_SKSTMO * AvgSellingPrc_R_TY_SKSTMO
Problems during preprocessing in REPL:
[ERROR] (row 0 and column 0 to row 0 and column 50) Operator multiply is not defined on arguments (int,decimal) in any of its defining predicates: int:multiply, int128:multiply, decimal:multiply, float:multiply.```

Remember that arithmetic operators, such as `*`, expect their operands to have the same type. You either multiply `int` with `int`, or `decimal` with `decimal`, but not `int` with `decimal`. Yet this is exactly what the rule above does: multiplying `SalesUnits_U_TY_SKSTMO`, which has value type `int`, with `AvgSellingPrc_R_TY_SKSTMO`, which has value type `decimal`.

So, before we can multiply these two measures with each other, we will need to convert one of them into the type of the other (e.g. `int` to `decimal`, or `decimal` to `int`). Since converting a decimal number to an integer would lose precision, we want to convert the integer sales units to decimals, instead. We can do this using the cast expression `as decimal`:

```> SalesUnits_U_TY_SKSTMO as decimal * AvgSellingPrc_R_TY_SKSTMO

Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊        "store-4" ┊       "2017-Jan" ┊         7162.62d
"Pink Grapefruit" ┊        "store-4" ┊       "2017-Jan" ┊         9806.82d
"Granny Smith" ┊        "store-4" ┊       "2017-Jan" ┊         2715.51d
"Honeyscrisp Apples" ┊        "store-4" ┊       "2017-Jan" ┊       134575.65d
"Beets" ┊        "store-4" ┊       "2017-Jan" ┊         81942.3d
"Clementines" ┊        "store-4" ┊       "2017-Jan" ┊        119979.2d
"Navel Oranges" ┊        "store-4" ┊       "2017-Jan" ┊        155669.4d
"Ginger" ┊        "store-4" ┊       "2017-Jan" ┊        88929.29d
"Radishes" ┊        "store-4" ┊       "2017-Jan" ┊          9833.4d
"Granny Smith" ┊        "store-1" ┊       "2017-Jan" ┊       130802.76d
"Ginger" ┊        "store-1" ┊       "2017-Jan" ┊       104841.66d
"Beets" ┊        "store-1" ┊       "2017-Jan" ┊        54947.96d
"Pink Grapefruit" ┊        "store-1" ┊       "2017-Jan" ┊        33764.52d
"Navel Oranges" ┊        "store-1" ┊       "2017-Jan" ┊        80589.59d
"Blood Orange" ┊        "store-1" ┊       "2017-Jan" ┊        10407.65d
"Radishes" ┊        "store-1" ┊       "2017-Jan" ┊        38483.95d
"Clementines" ┊        "store-1" ┊       "2017-Jan" ┊        46678.04d
"Ginger" ┊        "store-3" ┊       "2017-Jan" ┊        85930.74d
"Radishes" ┊        "store-3" ┊       "2017-Jan" ┊         30419.2d
"Pink Grapefruit" ┊        "store-3" ┊       "2017-Jan" ┊          1183.6d
"Granny Smith" ┊        "store-3" ┊       "2017-Jan" ┊       117062.81d
"Blood Orange" ┊        "store-3" ┊       "2017-Jan" ┊         4775.76d
"Honeyscrisp Apples" ┊        "store-1" ┊       "2017-Jan" ┊       132459.88d
"Clementines" ┊        "store-3" ┊       "2017-Jan" ┊       146984.77d
"Navel Oranges" ┊        "store-3" ┊       "2017-Jan" ┊        10309.52d
"Radishes" ┊       "store-13" ┊       "2017-Jan" ┊          1039.6d
"Clementines" ┊       "store-13" ┊       "2017-Jan" ┊       139910.43d
"Granny Smith" ┊       "store-13" ┊       "2017-Jan" ┊        40495.95d
"Ginger" ┊       "store-13" ┊       "2017-Jan" ┊         83379.1d
"Honeyscrisp Apples" ┊        "store-3" ┊       "2017-Jan" ┊       110469.96d

Displayed 30 out of 1296 result entries.```

### Modify Me

Add the rule from above in `<TRAINING_HOME>src/rules/tutorial4.logic`:

```^modeler_config:metamodel:cubiql:metric_expr["SalesRetail_R_TY_SKSTMO"] = """
SalesUnits_U_TY_SKSTMO as decimal * AvgSellingPrc_R_TY_SKSTMO
""".```

### Build Me

Run the commands below to load the new rule:

```\$ make load-cubiql-rules
\$ make load-test-data```

Open up the TY Data view and verify that the Sales \$ are now calculated correctly. Now that the Sales \$ are calculated, you can also validate that the Cost of Goods Sold (`COGS_R_TY_SKSTMO`) are correct!

## 3. Using Aggregations

So far our training application has only been using the default aggregation method for measures as defined in `<TRAINING_HOME>/src/config/Measures.csv`. This method is used by the Modeler to generate aggregations for a specific measure, based on the levels configured to be shown on a pivot grid. It is also necessary, sometimes, to use explicit aggregations in rules, such that a measure can be aggregated from a different measure.

Let's calculate for a given SKU, which store has the highest sales for that SKU in units. We have defined a measure `SkuBestSeller_B_SKST` in `<TRAINING_HOME>/src/config/Measures.csv` for this purpose:

`SkuBestSeller_B_SKST,Best-selling Store per SKU,SkuStore,,,none,none,,,,,center,true,,DerivedAndStored,,`

Notice that `SkuBestSeller_B_SKST` is keyed at `Sku`,`Store`. This measure has no value type, making it a position-only measure.

In order to compute the best selling store, we want to

• Calculate the units sold for each SKU/store over all months, and
• Calculate for each SKU, the store with the highest number of units sold.

### 3.1. Computing Sales Units per SKU and Store

Let's first find out how to calculate the units sold for each SKU and store.

In the previous tutorial, we've seen how to use aggregations in CubiQL expressions. Can you write a CubiQL expression that calculates the total of the Sales Units (`SalesUnits_U_TY_SKSTMO`) over all months?

### Run Me

```> total SalesUnits_U_TY_SKSTMO @ {Sku,Store}

Product : Sku ┊ Location : Store ┊ value of int
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Radishes" ┊        "store-1" ┊       182069
"Blood Orange" ┊        "store-1" ┊       161776
"Granny Smith" ┊        "store-1" ┊       166024
"Honeyscrisp Apples" ┊        "store-1" ┊       186505
"Clementines" ┊        "store-1" ┊       187165
"Navel Oranges" ┊        "store-1" ┊       191069
"Pink Grapefruit" ┊        "store-1" ┊       197111
"Blood Orange" ┊        "store-3" ┊       187164
"Navel Oranges" ┊        "store-3" ┊       216927
"Honeyscrisp Apples" ┊        "store-3" ┊       238202
"Radishes" ┊        "store-3" ┊       167242
"Beets" ┊        "store-1" ┊       223097
"Ginger" ┊        "store-1" ┊       231389
"Pink Grapefruit" ┊        "store-3" ┊       182353
"Clementines" ┊        "store-3" ┊       226774
"Navel Oranges" ┊       "store-13" ┊       150655
"Pink Grapefruit" ┊       "store-13" ┊       177654
"Radishes" ┊       "store-13" ┊       199790
"Blood Orange" ┊       "store-13" ┊       228184
"Ginger" ┊        "store-3" ┊       238223
"Granny Smith" ┊        "store-3" ┊       174676
"Clementines" ┊       "store-13" ┊       242671
"Beets" ┊        "store-3" ┊       218075
"Pink Grapefruit" ┊        "store-4" ┊       163500
"Clementines" ┊        "store-4" ┊       200543
"Blood Orange" ┊        "store-4" ┊       183662
"Navel Oranges" ┊        "store-4" ┊       162993
"Granny Smith" ┊       "store-13" ┊       159407
"Honeyscrisp Apples" ┊       "store-13" ┊       185173
"Beets" ┊       "store-13" ┊       183931

Displayed 30 out of 36 result entries.```

This expression aggregates away the `Calendar` dimension of the Sales Units measure (`SalesUnits_U_TY_SKSTMO` which is keyed by `Sku`,`Store`,`Month`) giving us the total number of units over all months per `Sku` and `Store`.

Remember when we discussed aggregations in the previous tutorial? Since `total` is the default aggregation method of `SalesUnits_U_TY_SKSTMO`, we could have also used the following syntax for our aggregation expression:

```>  SalesUnits_U_TY_SKSTMO[{Sku,Store}]

Product : Sku ┊ Location : Store ┊ value of int
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Navel Oranges" ┊       "store-13" ┊       150655
"Honeyscrisp Apples" ┊       "store-13" ┊       185173
"Beets" ┊       "store-13" ┊       183931
"Ginger" ┊       "store-13" ┊       166700
"Radishes" ┊       "store-13" ┊       199790
"Granny Smith" ┊       "store-13" ┊       159407
"Clementines" ┊       "store-13" ┊       242671
"Blood Orange" ┊       "store-13" ┊       228184
"Pink Grapefruit" ┊       "store-13" ┊       177654
"Beets" ┊        "store-1" ┊       223097
"Blood Orange" ┊        "store-1" ┊       161776
"Radishes" ┊        "store-1" ┊       182069
"Honeyscrisp Apples" ┊        "store-1" ┊       186505
"Clementines" ┊        "store-1" ┊       187165
"Navel Oranges" ┊        "store-1" ┊       191069
"Pink Grapefruit" ┊        "store-1" ┊       197111
"Granny Smith" ┊        "store-1" ┊       166024
"Blood Orange" ┊        "store-4" ┊       183662
"Pink Grapefruit" ┊        "store-4" ┊       163500
"Honeyscrisp Apples" ┊        "store-4" ┊       214527
"Beets" ┊        "store-4" ┊       237522
"Navel Oranges" ┊        "store-4" ┊       162993
"Ginger" ┊        "store-1" ┊       231389
"Granny Smith" ┊        "store-4" ┊       164780
"Clementines" ┊        "store-4" ┊       200543
"Pink Grapefruit" ┊        "store-3" ┊       182353
"Granny Smith" ┊        "store-3" ┊       174676
"Beets" ┊        "store-3" ┊       218075
"Blood Orange" ┊        "store-3" ┊       187164
"Ginger" ┊        "store-4" ┊       236888

Displayed 30 out of 36 result entries.```

### 3.2. Computing Best-selling Store per SKU

Now that we know the total sales units per SKU and store, we can calculate our best-selling store per SKU by using the `max` aggregation over the result of this expression.

Let's first put our calculation of units sold for each SKU and store in a `let` expression so we can easily reuse its results in our rule.

### Run Me

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
SalesUnits_U_SKST = ( total SalesUnits_U_TY_SKSTMO @ {Sku,Store} )
) in (
SalesUnits_U_SKST
)
// Exiting multi-line mode, now evaluating.

Product : Sku ┊ Location : Store ┊ value of int
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Clementines" ┊        "store-1" ┊       187165
"Beets" ┊        "store-1" ┊       223097
"Pink Grapefruit" ┊        "store-1" ┊       197111
"Blood Orange" ┊        "store-1" ┊       161776
"Honeycrisp Apples" ┊        "store-1" ┊       186505
"Ginger" ┊        "store-1" ┊       231389
"Granny Smith" ┊        "store-1" ┊       166024
"Beets" ┊       "store-13" ┊       183931
"Ginger" ┊       "store-13" ┊       166700
"Blood Orange" ┊       "store-13" ┊       228184
"Clementines" ┊       "store-13" ┊       242671
"Radishes" ┊        "store-1" ┊       182069
"Navel Oranges" ┊        "store-1" ┊       191069
"Granny Smith" ┊       "store-13" ┊       159407
"Honeycrisp Apples" ┊       "store-13" ┊       185173
"Ginger" ┊        "store-4" ┊       236888
"Granny Smith" ┊        "store-4" ┊       164780
"Clementines" ┊        "store-4" ┊       200543
"Beets" ┊        "store-4" ┊       237522
"Navel Oranges" ┊       "store-13" ┊       150655
"Pink Grapefruit" ┊       "store-13" ┊       177654
"Honeycrisp Apples" ┊        "store-4" ┊       214527
"Radishes" ┊       "store-13" ┊       199790
"Granny Smith" ┊        "store-3" ┊       174676
"Honeycrisp Apples" ┊        "store-3" ┊       238202
"Beets" ┊        "store-3" ┊       218075
"Ginger" ┊        "store-3" ┊       238223
"Pink Grapefruit" ┊        "store-4" ┊       163500
"Blood Orange" ┊        "store-4" ┊       183662
"Radishes" ┊        "store-4" ┊       184360

Displayed 30 out of 36 result entries.```

Can you write a CubiQL expression that calculates the best-selling store? You can take a look at the expressions below if you need some help.

### Run Me

First, let's try to find the highest number of Sales Units per `Sku` using the `max` aggregation:

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
SalesUnits_U_SKST = ( total SalesUnits_U_TY_SKSTMO @ {Sku,Store} )
) in (
max SalesUnits_U_SKST @ { Sku }
)
// Exiting multi-line mode, now evaluating.

Product : Sku ┊ value of int
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Honeycrisp Apples" ┊       238202
"Granny Smith" ┊       174676
"Ginger" ┊       238223
"Beets" ┊       237522
"Clementines" ┊       242671
"Blood Orange" ┊       228184
"Pink Grapefruit" ┊       197111
"Navel Oranges" ┊       216927
"Radishes" ┊       199790

Displayed all 9 result entries.```

Perfect, we've aggregated away the `Location` dimension giving us the maximum number of Sales Units per `Sku`.

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
SalesUnits_U_SKST = ( total SalesUnits_U_TY_SKSTMO @ {Sku,Store} ),
maxSales = ( max SalesUnits_U_SKST @ { Sku } )
) in (
filter SalesUnits_U_SKST by = maxSales
)
// Exiting multi-line mode, now evaluating.

Product : Sku ┊ Location : Store ┊ value of int
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊       "store-13" ┊       228184
"Clementines" ┊       "store-13" ┊       242671
"Beets" ┊        "store-4" ┊       237522
"Radishes" ┊       "store-13" ┊       199790
"Pink Grapefruit" ┊        "store-1" ┊       197111
"Granny Smith" ┊        "store-3" ┊       174676
"Honeycrisp Apples" ┊        "store-3" ┊       238202
"Navel Oranges" ┊        "store-3" ┊       216927
"Ginger" ┊        "store-3" ┊       238223

Displayed all 9 result entries.```

First, our expression bound to `maxSales` aggregates away the `Location` dimension of the Sales Units measure (`SalesUnits_U_SKST` which is keyed by `Sku`,`Store`) giving us the maximum number of units per `Sku` over all stores. We then use a `filter` expression to return only those `Sku` and `Store` combinations which have a value equal to the maximum number of units over all stores for those SKU's.

### Modify Me

Ok, let's add a rule for measure `SkuBestSeller_B_SKST` to `<TRAINING_HOME>src/rules/tutorial4.logic`:

```^modeler_config:metamodel:cubiql:metric_expr["SkuBestSeller_B_SKST"] = """
let (
SalesUnits_U_SKST = ( total SalesUnits_U_TY_SKSTMO @ {Sku,Store} ),
maxSales = ( max SalesUnits_U_SKST @ { Sku } )
) in (
#( filter SalesUnits_U_SKST by = maxSales )
)
""".```

As you might have noticed, we remove the values from the result of our expression using a `drop` expression (`#..`). This is required because `SkuBestSeller_B_SKST` is a position-only measure.

### Build Me

Run the commands below:

```\$ make load-cubiql-rules
\$ make load-test-data```

Go to the Ad Hoc Canvas and configure the view to look similar to the figure below. Can you see and verify per SKU which store has the highest sales?

## 4. Using Slides

### 4.1. Computing Sales \$ Trend (TY)

Let's say we're interested in seeing how our monthly `Sales \$` for a `Sku`/`Store` relates to the `Sales \$` in the previous month. We can use a measure to hold the value `+`, if the `Sales \$` for a month is higher than for the previous month, and `-` if the value is lower. For this purpose, we have specified a measure called `SalesRetailTrend_TY_SKSTMO` in `<TRAINING_HOME>/src/config/Measures.csv`:

`SalesRetailTrend_TY_SKSTMO,Sales \$ Trend,SkuStoreMonth,string,,ambig,none,,,,,center,true,,DerivedAndStored,,`

As you can see, this measure is defined at the intersection `Sku`,`Store`,`Month`, and has the value type `string`.

As a starting point for this exercise, let's find out how to define a valued literal measures in CubiQL that hold the value `-` and `+` for all positions on the `Sku`,`Store`,`Month` intersection.

Do you remember the previous tutorial, where we discussed literal measures? The string `"+"` can also be written as `{ ("+") } : {} => string`. So, the string `"+"` is a measure at the top intersection `{}` where there are no keys. We can use a `refine` expression to distribute the value at the top intersection to all positions at the `Sku`,`Store`,`Month` intersection.

### Run Me

```> refine "+" @ {Sku,Store,Month}

Calendar : Month ┊ Location : Store ┊        Product : Sku ┊ value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"2017-Jan" ┊        "store-1" ┊ "Honeyscrisp Apples" ┊             "+"
"2017-Jan" ┊        "store-1" ┊       "Blood Orange" ┊             "+"
"2017-Jan" ┊        "store-1" ┊             "Ginger" ┊             "+"
"2017-Jan" ┊        "store-1" ┊       "Granny Smith" ┊             "+"
"2017-Jan" ┊        "store-3" ┊       "Blood Orange" ┊             "+"
"2017-Jan" ┊        "store-1" ┊           "Radishes" ┊             "+"
"2017-Jan" ┊        "store-3" ┊       "Granny Smith" ┊             "+"
"2017-Jan" ┊        "store-3" ┊ "Honeyscrisp Apples" ┊             "+"
"2017-Jan" ┊        "store-1" ┊        "Clementines" ┊             "+"
"2017-Jan" ┊        "store-1" ┊              "Beets" ┊             "+"
"2017-Jan" ┊        "store-1" ┊      "Navel Oranges" ┊             "+"
"2017-Jan" ┊        "store-1" ┊    "Pink Grapefruit" ┊             "+"
"2017-Jan" ┊        "store-3" ┊           "Radishes" ┊             "+"
"2017-Jan" ┊        "store-3" ┊      "Navel Oranges" ┊             "+"
"2017-Jan" ┊       "store-13" ┊ "Honeyscrisp Apples" ┊             "+"
"2017-Jan" ┊       "store-13" ┊       "Blood Orange" ┊             "+"
"2017-Jan" ┊        "store-3" ┊              "Beets" ┊             "+"
"2017-Jan" ┊        "store-3" ┊             "Ginger" ┊             "+"
"2017-Jan" ┊        "store-3" ┊    "Pink Grapefruit" ┊             "+"
"2017-Jan" ┊        "store-3" ┊        "Clementines" ┊             "+"
"2017-Jan" ┊       "store-13" ┊      "Navel Oranges" ┊             "+"
"2017-Jan" ┊       "store-13" ┊    "Pink Grapefruit" ┊             "+"
"2017-Jan" ┊        "store-4" ┊       "Blood Orange" ┊             "+"
"2017-Jan" ┊       "store-13" ┊           "Radishes" ┊             "+"
"2017-Jan" ┊       "store-13" ┊             "Ginger" ┊             "+"
"2017-Jan" ┊       "store-13" ┊       "Granny Smith" ┊             "+"
"2017-Jan" ┊       "store-13" ┊        "Clementines" ┊             "+"
"2017-Jan" ┊       "store-13" ┊              "Beets" ┊             "+"
"2017-Jan" ┊        "store-4" ┊    "Pink Grapefruit" ┊             "+"
"2017-Jan" ┊        "store-4" ┊        "Clementines" ┊             "+"

Displayed 30 out of 1296 result entries.```

Similarly, we can query a measure holding `-` for every position:

```> refine "-" @ {Sku,Store,Month}

Calendar : Month ┊ Location : Store ┊        Product : Sku ┊ value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"2017-Jan" ┊        "store-1" ┊ "Honeyscrisp Apples" ┊             "-"
"2017-Jan" ┊        "store-1" ┊       "Blood Orange" ┊             "-"
"2017-Jan" ┊        "store-1" ┊      "Navel Oranges" ┊             "-"
"2017-Jan" ┊        "store-1" ┊    "Pink Grapefruit" ┊             "-"
"2017-Jan" ┊        "store-3" ┊       "Blood Orange" ┊             "-"
"2017-Jan" ┊        "store-1" ┊           "Radishes" ┊             "-"
"2017-Jan" ┊        "store-1" ┊             "Ginger" ┊             "-"
"2017-Jan" ┊        "store-1" ┊       "Granny Smith" ┊             "-"
"2017-Jan" ┊        "store-1" ┊        "Clementines" ┊             "-"
"2017-Jan" ┊        "store-1" ┊              "Beets" ┊             "-"
"2017-Jan" ┊        "store-3" ┊    "Pink Grapefruit" ┊             "-"
"2017-Jan" ┊        "store-3" ┊        "Clementines" ┊             "-"
"2017-Jan" ┊        "store-3" ┊           "Radishes" ┊             "-"
"2017-Jan" ┊        "store-3" ┊      "Navel Oranges" ┊             "-"
"2017-Jan" ┊        "store-3" ┊       "Granny Smith" ┊             "-"
"2017-Jan" ┊        "store-3" ┊ "Honeyscrisp Apples" ┊             "-"
"2017-Jan" ┊        "store-3" ┊              "Beets" ┊             "-"
"2017-Jan" ┊        "store-3" ┊             "Ginger" ┊             "-"
"2017-Jan" ┊       "store-13" ┊        "Clementines" ┊             "-"
"2017-Jan" ┊       "store-13" ┊              "Beets" ┊             "-"
"2017-Jan" ┊       "store-13" ┊      "Navel Oranges" ┊             "-"
"2017-Jan" ┊       "store-13" ┊    "Pink Grapefruit" ┊             "-"
"2017-Jan" ┊       "store-13" ┊ "Honeyscrisp Apples" ┊             "-"
"2017-Jan" ┊       "store-13" ┊       "Blood Orange" ┊             "-"
"2017-Jan" ┊       "store-13" ┊             "Ginger" ┊             "-"
"2017-Jan" ┊       "store-13" ┊       "Granny Smith" ┊             "-"
"2017-Jan" ┊        "store-4" ┊              "Beets" ┊             "-"
"2017-Jan" ┊        "store-4" ┊             "Ginger" ┊             "-"
"2017-Jan" ┊        "store-4" ┊    "Pink Grapefruit" ┊             "-"
"2017-Jan" ┊        "store-4" ┊        "Clementines" ┊             "-"

Displayed 30 out of 1296 result entries.```

For our rule, we now need to find a way to filter out positions that not meet our conditions. To achieve this, we need a way of comparing this month's `Sales \$` with last month's `Sales \$`.

We can do such a comparison by collecting last month's `Sales \$` for a given `Month` using a slide. A slide, in general, is a tool to define an aggregation step outside of the predefined level-to-level mappings (e.g. `Sku` to `Subclass`) in the hierarchy of a dimension. For our rule, this slide needs to move positions of the `Month` level of the `Calendar` dimension (which constitutes a one-dimensional intersection) to different positions in the same `Month` level.

Luckily, Modeler generates slides for ordered levels such as `Month` which we can use for time-shifting data. In this case, we can use the auto-generated slide `Calendar_Month_next` to obtain a measure in which the values at a given `Month` are time-shifted to the next `Month`. This will get us exactly what we need, the values at a given `Month` (e.g. July 2017) of the resulting measure are the same as the values at the `Month` before (e.g. June 2017) in the queried measure.

### Run Me

```> collect SalesRetail_R_TY_SKSTMO by slide Calendar_Month_next

Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Granny Smith" ┊        "store-4" ┊       "2017-Mar" ┊         21364.2d
"Beets" ┊        "store-4" ┊       "2017-Mar" ┊        73527.74d
"Pink Grapefruit" ┊        "store-4" ┊       "2017-Mar" ┊          5654.5d
"Ginger" ┊        "store-4" ┊       "2017-Mar" ┊        14763.69d
"Radishes" ┊        "store-4" ┊       "2017-Mar" ┊         3044.36d
"Pink Grapefruit" ┊        "store-3" ┊       "2017-Mar" ┊         9459.99d
"Granny Smith" ┊        "store-3" ┊       "2017-Mar" ┊       119128.24d
"Blood Orange" ┊        "store-4" ┊       "2017-Mar" ┊         7916.04d
"Honeyscrisp Apples" ┊        "store-4" ┊       "2017-Mar" ┊          3414.4d
"Clementines" ┊        "store-4" ┊       "2017-Mar" ┊       212478.24d
"Navel Oranges" ┊        "store-4" ┊       "2017-Mar" ┊          1699.4d
"Radishes" ┊        "store-3" ┊       "2017-Mar" ┊        18063.66d
"Clementines" ┊        "store-3" ┊       "2017-Mar" ┊         11025.0d
"Granny Smith" ┊        "store-1" ┊       "2017-Mar" ┊        71111.94d
"Ginger" ┊        "store-3" ┊       "2017-Mar" ┊        20653.75d
"Honeyscrisp Apples" ┊        "store-3" ┊       "2017-Mar" ┊        29441.88d
"Beets" ┊        "store-3" ┊       "2017-Mar" ┊        57162.03d
"Navel Oranges" ┊        "store-3" ┊       "2017-Mar" ┊        61966.12d
"Blood Orange" ┊        "store-3" ┊       "2017-Mar" ┊        26591.45d
"Clementines" ┊        "store-1" ┊       "2017-Mar" ┊       162249.36d
"Navel Oranges" ┊        "store-1" ┊       "2017-Mar" ┊       148512.39d
"Ginger" ┊        "store-1" ┊       "2017-Mar" ┊        109370.8d
"Radishes" ┊        "store-1" ┊       "2017-Mar" ┊        25628.49d
"Beets" ┊        "store-1" ┊       "2017-Mar" ┊         50808.6d
"Pink Grapefruit" ┊        "store-1" ┊       "2017-Mar" ┊        63884.61d
"Blood Orange" ┊        "store-1" ┊       "2017-Mar" ┊        14242.89d
"Honeyscrisp Apples" ┊        "store-1" ┊       "2017-Mar" ┊        140985.6d
"Navel Oranges" ┊       "store-13" ┊       "2017-Mar" ┊        20968.64d
"Blood Orange" ┊       "store-13" ┊       "2017-Mar" ┊        26929.55d
"Radishes" ┊       "store-13" ┊       "2017-Mar" ┊         1826.16d

Displayed 30 out of 1260 result entries.```

You could zoom in on some positions to see this slide in action. First, let's query our `Sales \$` for Blood Oranges (`sku-01`) sold at `store-1` in the months June 2017 (`2017-06`), July 2017 (`2017-07`), August 2017 (`2017-08`), September 2017 (`2017-09`), and October 2017 (`2017-10`).

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
sales = ( dice SalesRetail_R_TY_SKSTMO by {("sku-01")}:{Sku} and {("store-1")}:{Store} and {("2017-06"),("2017-07"),("2017-08"),("2017-09"),("2017-10")}:{Month} )
) in (
sales
)
// Exiting multi-line mode, now evaluating.

Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊        "store-1" ┊       "2017-Aug" ┊          292.82d
"Blood Orange" ┊        "store-1" ┊       "2017-Jul" ┊        15976.84d
"Blood Orange" ┊        "store-1" ┊       "2017-Oct" ┊        35083.62d
"Blood Orange" ┊        "store-1" ┊       "2017-Sep" ┊        13268.08d
"Blood Orange" ┊        "store-1" ┊       "2017-Jun" ┊        19512.84d

Displayed all 5 result entries.```

Then, look at the results when you use the `Calendar_Month_next` slide.

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
sales = ( dice SalesRetail_R_TY_SKSTMO by {("sku-01")}:{Sku} and {("store-1")}:{Store} and {("2017-06"),("2017-07"),("2017-08"),("2017-09"),("2017-10")}:{Month} )
) in (
collect sales by slide Calendar_Month_next
)
// Exiting multi-line mode, now evaluating.

Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊        "store-1" ┊       "2017-Nov" ┊        35083.62d
"Blood Orange" ┊        "store-1" ┊       "2017-Jul" ┊        19512.84d
"Blood Orange" ┊        "store-1" ┊       "2017-Oct" ┊        13268.08d
"Blood Orange" ┊        "store-1" ┊       "2017-Aug" ┊        15976.84d
"Blood Orange" ┊        "store-1" ┊       "2017-Sep" ┊          292.82d

Displayed all 5 result entries.```

In the measure returned by this query, the `Sales \$` of June 2017 (`19512.84` in measure `SalesRetail_R_TY_SKSTMO`) can be found at the position of July 2018.

Ok, let's summarize what we've learned so far.

We know that our training application contains the measure `SalesRetail_R_TY_SKSTMO` which contains all relevant `Sales \$` data:

```let (
sales = SalesRetail_R_TY_SKSTMO,
..
) in (
..
)
```

We now know how to define two literal values measures that hold `+` and `-` for each position at the `Sku`,`Store`,`Month` intersection:

```let (
sales = SalesRetail_R_TY_SKSTMO,
increased = ( refine "+" @{Sku,Store,Month} ),
decreased = ( refine "-" @ {Sku,Store,Month} ),
..
) in (
..
)
```

And, we've learned how to use the auto-generated slide `Calendar_Month_next` to query a measure which for each month holds last month's `Sales \$` (e.g. all positions at July 2017 contain the `Sales \$` value for June 2017):

```let (
sales = SalesRetail_R_TY_SKSTMO,
increased = ( refine "+" @{Sku,Store,Month} ),
decreased = ( refine "-" @ {Sku,Store,Month} ),
salesLastMonth = ( collect sales by slide Calendar_Month_next ),
..
) in (
..
)
```

Now, for each position, we can compare the values of `sales` with the values in `salesLastMonth`. When for some month, the value of `sales` is higher than the value in `salesLastMonth`, we know the sales have increased. We can use a `filter` expression to implement this comparison:

```let (
sales = SalesRetail_R_TY_SKSTMO,
increased = ( refine "+" @{Sku,Store,Month} ),
decreased = ( refine "-" @ {Sku,Store,Month} ),
salesLastMonth = ( collect sales by slide Calendar_Month_next ),
salesIncreased = ( filter sales by > salesLastMonth )
) in (
..
)
```

All that remains now, is to `dice` our literal measure `increased` so it will only contain `+` for positions where the sales increased.

### Run Me

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
sales = SalesRetail_R_TY_SKSTMO,
increased = ( refine "+" @{Sku,Store,Month} ),
decreased = ( refine "-" @ {Sku,Store,Month} ),
salesLastMonth = ( collect sales by slide Calendar_Month_next ),
salesIncreased = ( filter sales by > salesLastMonth )
) in (
dice increased by salesIncreased
)
// Exiting multi-line mode, now evaluating.

Calendar : Month ┊ Location : Store ┊        Product : Sku ┊ value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"2017-Feb" ┊        "store-1" ┊        "Clementines" ┊             "+"
"2017-Feb" ┊        "store-1" ┊ "Honeyscrisp Apples" ┊             "+"
"2017-Feb" ┊        "store-1" ┊             "Ginger" ┊             "+"
"2017-Feb" ┊        "store-1" ┊       "Blood Orange" ┊             "+"
"2017-Feb" ┊       "store-13" ┊           "Radishes" ┊             "+"
"2017-Feb" ┊       "store-13" ┊        "Clementines" ┊             "+"
"2017-Feb" ┊        "store-1" ┊      "Navel Oranges" ┊             "+"
"2017-Feb" ┊        "store-1" ┊    "Pink Grapefruit" ┊             "+"
"2017-Feb" ┊        "store-3" ┊       "Granny Smith" ┊             "+"
"2017-Feb" ┊        "store-3" ┊      "Navel Oranges" ┊             "+"
"2017-Apr" ┊       "store-13" ┊       "Granny Smith" ┊             "+"
"2017-Feb" ┊        "store-3" ┊       "Blood Orange" ┊             "+"
"2017-Feb" ┊        "store-4" ┊        "Clementines" ┊             "+"
"2017-Feb" ┊        "store-4" ┊       "Granny Smith" ┊             "+"
"2017-Feb" ┊        "store-3" ┊    "Pink Grapefruit" ┊             "+"
"2017-Feb" ┊        "store-4" ┊       "Blood Orange" ┊             "+"
"2017-Apr" ┊        "store-1" ┊              "Beets" ┊             "+"
"2017-Apr" ┊        "store-1" ┊        "Clementines" ┊             "+"
"2017-Apr" ┊        "store-4" ┊      "Navel Oranges" ┊             "+"
"2017-Apr" ┊        "store-1" ┊           "Radishes" ┊             "+"
"2017-Apr" ┊       "store-13" ┊       "Blood Orange" ┊             "+"
"2017-Apr" ┊       "store-13" ┊        "Clementines" ┊             "+"
"2017-Apr" ┊       "store-13" ┊           "Radishes" ┊             "+"
"2017-Apr" ┊       "store-13" ┊             "Ginger" ┊             "+"
"2017-Apr" ┊        "store-3" ┊       "Blood Orange" ┊             "+"
"2017-Apr" ┊        "store-3" ┊ "Honeyscrisp Apples" ┊             "+"
"2017-Apr" ┊        "store-3" ┊              "Beets" ┊             "+"
"2017-Apr" ┊        "store-3" ┊             "Ginger" ┊             "+"
"2017-Apr" ┊        "store-4" ┊       "Granny Smith" ┊             "+"
"2017-Apr" ┊        "store-4" ┊ "Honeyscrisp Apples" ┊             "+"

Displayed 30 out of 214 result entries.```

Remember that we also wanted our resulting measure to hold `-` for positions where sales decreased. To achieve this, we can also apply a `dice` expression on the literal measure `decreased`. To combine the results of both `dice` expressions, we can use a union `|`:

### Run Me

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
sales = SalesRetail_R_TY_SKSTMO,
increased = ( refine "+" @{Sku,Store,Month} ),
decreased = ( refine "-" @ {Sku,Store,Month} ),
salesLastMonth = ( collect sales by slide Calendar_Month_next ),
salesIncreased = ( filter sales by > salesLastMonth ),
salesDecreased = ( filter sales by < salesLastMonth )
) in (
( dice increased by salesIncreased )
|
( dice decreased by salesDecreased )
)
// Exiting multi-line mode, now evaluating.

Calendar : Month ┊ Location : Store ┊        Product : Sku ┊ value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"2017-Feb" ┊        "store-1" ┊             "Ginger" ┊         { "+" }
"2017-Feb" ┊        "store-1" ┊       "Granny Smith" ┊         { "-" }
"2017-Feb" ┊        "store-1" ┊        "Clementines" ┊         { "+" }
"2017-Feb" ┊        "store-1" ┊              "Beets" ┊         { "-" }
"2017-Feb" ┊        "store-1" ┊ "Honeyscrisp Apples" ┊         { "+" }
"2017-Feb" ┊        "store-1" ┊       "Blood Orange" ┊         { "+" }
"2017-Feb" ┊        "store-3" ┊       "Granny Smith" ┊         { "+" }
"2017-Feb" ┊        "store-3" ┊ "Honeyscrisp Apples" ┊         { "-" }
"2017-Feb" ┊        "store-3" ┊              "Beets" ┊         { "-" }
"2017-Feb" ┊        "store-3" ┊             "Ginger" ┊         { "-" }
"2017-Feb" ┊        "store-1" ┊      "Navel Oranges" ┊         { "+" }
"2017-Feb" ┊        "store-1" ┊    "Pink Grapefruit" ┊         { "+" }
"2017-Feb" ┊        "store-3" ┊       "Blood Orange" ┊         { "+" }
"2017-Feb" ┊        "store-1" ┊           "Radishes" ┊         { "-" }
"2017-Feb" ┊       "store-13" ┊ "Honeyscrisp Apples" ┊         { "-" }
"2017-Feb" ┊       "store-13" ┊       "Blood Orange" ┊         { "-" }
"2017-Feb" ┊       "store-13" ┊             "Ginger" ┊         { "-" }
"2017-Feb" ┊       "store-13" ┊       "Granny Smith" ┊         { "-" }
"2017-Feb" ┊        "store-3" ┊    "Pink Grapefruit" ┊         { "+" }
"2017-Feb" ┊        "store-3" ┊        "Clementines" ┊         { "-" }
"2017-Feb" ┊        "store-3" ┊           "Radishes" ┊         { "-" }
"2017-Feb" ┊        "store-3" ┊      "Navel Oranges" ┊         { "+" }
"2017-Feb" ┊        "store-4" ┊       "Blood Orange" ┊         { "+" }
"2017-Feb" ┊       "store-13" ┊           "Radishes" ┊         { "+" }
"2017-Feb" ┊        "store-4" ┊       "Granny Smith" ┊         { "+" }
"2017-Feb" ┊        "store-4" ┊ "Honeyscrisp Apples" ┊         { "-" }
"2017-Feb" ┊       "store-13" ┊        "Clementines" ┊         { "+" }
"2017-Feb" ┊       "store-13" ┊              "Beets" ┊         { "-" }
"2017-Feb" ┊       "store-13" ┊      "Navel Oranges" ┊         { "-" }
"2017-Feb" ┊       "store-13" ┊    "Pink Grapefruit" ┊         { "-" }

Displayed 30 out of 468 result entries.```

Hm, do you notice that the results are set-valued `{ "+" , .. }` instead of being single-valued (e.g `"+"`)?

This is caused by the union which is always considered set-valued, even if the actual result does not contain any position with multiple entries at it. However, we know that sales cannot ever both increase and decrease and thus we know that the result of our union will always be single-valued. To recover the single-valued result, we can add the `{{ functional = true }}` annotation to the expression.

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
sales = SalesRetail_R_TY_SKSTMO,
increased = ( refine "+" @{Sku,Store,Month} ),
decreased = ( refine "-" @ {Sku,Store,Month} ),
salesLastMonth = ( collect sales by slide Calendar_Month_next ),
salesIncreased = ( filter sales by > salesLastMonth ),
salesDecreased = ( filter sales by < salesLastMonth )
) in (
{{ functional = true }}
( dice increased by salesIncreased )
|
( dice decreased by salesDecreased )
)
// Exiting multi-line mode, now evaluating.

Calendar : Month ┊ Location : Store ┊        Product : Sku ┊ value of string
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"2017-Feb" ┊        "store-1" ┊        "Clementines" ┊             "+"
"2017-Feb" ┊        "store-1" ┊              "Beets" ┊             "-"
"2017-Feb" ┊        "store-1" ┊      "Navel Oranges" ┊             "+"
"2017-Feb" ┊        "store-1" ┊    "Pink Grapefruit" ┊             "+"
"2017-Feb" ┊        "store-1" ┊ "Honeyscrisp Apples" ┊             "+"
"2017-Feb" ┊        "store-1" ┊       "Blood Orange" ┊             "+"
"2017-Feb" ┊        "store-1" ┊             "Ginger" ┊             "+"
"2017-Feb" ┊        "store-1" ┊       "Granny Smith" ┊             "-"
"2017-Feb" ┊        "store-3" ┊              "Beets" ┊             "-"
"2017-Feb" ┊        "store-3" ┊             "Ginger" ┊             "-"
"2017-Feb" ┊        "store-3" ┊    "Pink Grapefruit" ┊             "+"
"2017-Feb" ┊        "store-3" ┊        "Clementines" ┊             "-"
"2017-Feb" ┊        "store-3" ┊       "Blood Orange" ┊             "+"
"2017-Feb" ┊        "store-1" ┊           "Radishes" ┊             "-"
"2017-Feb" ┊        "store-3" ┊       "Granny Smith" ┊             "+"
"2017-Feb" ┊        "store-3" ┊ "Honeyscrisp Apples" ┊             "-"
"2017-Feb" ┊       "store-13" ┊             "Ginger" ┊             "-"
"2017-Feb" ┊       "store-13" ┊       "Granny Smith" ┊             "-"
"2017-Feb" ┊       "store-13" ┊        "Clementines" ┊             "+"
"2017-Feb" ┊       "store-13" ┊              "Beets" ┊             "-"
"2017-Feb" ┊        "store-3" ┊           "Radishes" ┊             "-"
"2017-Feb" ┊        "store-3" ┊      "Navel Oranges" ┊             "+"
"2017-Feb" ┊       "store-13" ┊ "Honeyscrisp Apples" ┊             "-"
"2017-Feb" ┊       "store-13" ┊       "Blood Orange" ┊             "-"
"2017-Feb" ┊        "store-4" ┊       "Granny Smith" ┊             "+"
"2017-Feb" ┊        "store-4" ┊ "Honeyscrisp Apples" ┊             "-"
"2017-Feb" ┊        "store-4" ┊              "Beets" ┊             "-"
"2017-Feb" ┊        "store-4" ┊             "Ginger" ┊             "-"
"2017-Feb" ┊       "store-13" ┊      "Navel Oranges" ┊             "-"
"2017-Feb" ┊       "store-13" ┊    "Pink Grapefruit" ┊             "-"

Displayed 30 out of 468 result entries.```

Now that we've created and tested our rule, we can configure it in our training application!

### Modify Me

Add the following rule to `<TRAINING_HOME>src/rules/tutorial4.logic`:

```^modeler_config:metamodel:cubiql:metric_expr["SalesRetailTrend_TY_SKSTMO"] = """
let (
sales = SalesRetail_R_TY_SKSTMO,
increased = ( refine "+" @{Sku,Store,Month} ),
decreased = ( refine "-" @ {Sku,Store,Month} ),
salesLastMonth = ( collect sales by slide Calendar_Month_next ),
salesIncreased = ( filter sales by > salesLastMonth ),
salesDecreased = ( filter sales by < salesLastMonth )
) in (
{{ functional = true }}
( dice increased by salesIncreased )
|
( dice decreased by salesDecreased )
)
""".```

### Build Me

Run the commands below:

```  \$ make load-cubiql-rules
\$ make load-test-data ```

Go to an Ad Hoc Canvas and configure the view to look similar to the figure below to check out the results of your business rule.

### 4.2. Time-Shifting Data (LY)

The LY version of a measure typically contains the values of the measure at the same time "Last Year". For instance, the LY version of `Sales Units` in January 2018 should contain the actual value (TY version) of `Sales Units` in January 2017. We have specified five LY measures in `<TRAINING_HOME>/src/config/Measures.csv`, for which we will define rules for in this lesson:

```SalesUnits_U_LY_SKSTMO,Sales Units LY,SkuStoreMonth,int,0,total,ratio,,,,"{""thousandSeparated"":true,""mantissa"":0}",right,true,,DerivedAndStored,,
MarginPct_P_LY_SKSTMO,Gross Margin % LY,SkuStoreMonth,decimal,0d,average,replicate,,,,"{""thousandSeparated"":true,""mantissa"":1,""output"":""percent""}",right,true,,DerivedAndStored,,
AvgSellingPrc_R_LY_SKSTMO,Average Selling Price LY,SkuStoreMonth,decimal,0d,average,replicate,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""\$"",""thousandSeparated"":true,""mantissa"":2}",right,true,,DerivedAndStored,,
COGS_R_LY_SKSTMO,Cost of Goods Sold LY,SkuStoreMonth,decimal,0d,total,ratio,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""\$"",""thousandSeparated"":true,""mantissa"":2}",right,true,,DerivedAndStored,,
SalesRetail_R_LY_SKSTMO,Sales \$ LY,SkuStoreMonth,decimal,0d,total,ratio,,,,"{""currencyPosition"":""prefix"",""currencySymbol"":""\$"",""thousandSeparated"":true,""mantissa"":2}",right,true,,DerivedAndStored,,```

These measures have an almost identical specification to their TY versions. We have replaced the `TY` in both the measure name as well as the label with `LY`. All other properties, such as the intersection, data type, and default value remain the same.

Let's find out how to time-shift the data stored in TY measures to their corresponding LY measures. First, we calculate the values for `SalesUnits_U_LY_SKSTMO` from the values of its `TY` version, `SalesUnits_U_TY_SKSTMO`: we want the LY measure to contain the values of `SalesUnits_U_TY_SKSTMO` for the same month last year (i.e. 12 months ago).

In the previous lesson, we've learned how to time-shift data using the slide `Calendar_Month_next`. Unfortunately for us, we can't use the auto-generated slide called `Calendar_Year_next` to solve our problem since this slide has no notion of how `Month` levels relate to each other. Instead, we need to define a slide ourselves which tells the measure service what's the same month last year for a given month.

Let's start by configuring the slide in our training application:

### Modify Me

Add the following logic to `<TRAINING_HOME>/src/rules/tutorial4.logic`:

```+util:slides:add_slide(
"Calendar_Month_next_year",
"util:model:Calendar_Month_next_year",
"Calendar", "Month",
"Calendar", "Month").
```

We use the predicate `util:slides:add_slide` to configure the slide. You can find it's definition and related logic in `<TRAINING_HOME>/src/logiql/util/slides.logic`. We pass the following information to this predicate:

• `Calendar_Month_next_year` - the name of the slide which we will use in our CubiQL expressions (e.g. `collect SalesUnits_U_TY_SKSTMO by slide Calendar_Month_next_year`).
• `util:model:Calendar_Month_next_year` - the name of the predicate which maps positions from one intersection to positions in another.
• `Calendar:Month` - the source level of the slide.
• `Calendar:Month` - the target level of the slide. In our case, the target level is the same as the source level since our predicate defines a mapping between `Month` levels.

In the "base" of this tutorial, we've already prepared the predicate `util:model:Calendar_Month_next_year`. You can find its definition in `<TRAINING_HOME>/src/logiql/util/model.logic`:

`Calendar_Month_next_year[x] = y -> Calendar:Month(x), Calendar:Month(y).`

When you provide this predicate with some `Month` it will return its corresponding `Month` on year in the future. This rule uses another auto-generated predicate, `Calendar:Month_index`. For any given `Month`, the same `Month` next year is exactly 12 months later in terms of index.

```Calendar_Month_next_year[x] = y <-
Calendar:Month_index[x] + 12 = Calendar:Month_index[y].```

### Build Me

Run the commands below:

```\$ make load-cubiql-rules
\$ make load-test-data```

Ok, let's open the CubiQL REPL and see our new slide in action!

### Run Me

```> collect SalesUnits_U_TY_SKSTMO by slide Calendar_Month_next_year

Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of int
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Clementines" ┊        "store-1" ┊       "2017-May" ┊            0
"Blood Orange" ┊        "store-1" ┊       "2017-May" ┊            0
"Beets" ┊        "store-1" ┊       "2017-May" ┊            0
"Ginger" ┊        "store-1" ┊       "2017-May" ┊            0
"Navel Oranges" ┊        "store-1" ┊       "2017-May" ┊            0
"Pink Grapefruit" ┊        "store-1" ┊       "2017-May" ┊            0
"Granny Smith" ┊        "store-1" ┊       "2017-May" ┊            0
"Honeyscrisp Apples" ┊        "store-1" ┊       "2017-May" ┊            0
"Blood Orange" ┊        "store-3" ┊       "2017-May" ┊            0
"Granny Smith" ┊        "store-3" ┊       "2017-May" ┊            0
"Ginger" ┊        "store-3" ┊       "2017-May" ┊            0
"Clementines" ┊        "store-3" ┊       "2017-May" ┊            0
"Pink Grapefruit" ┊        "store-3" ┊       "2017-May" ┊            0
"Radishes" ┊        "store-1" ┊       "2017-May" ┊            0
"Honeyscrisp Apples" ┊        "store-3" ┊       "2017-May" ┊            0
"Navel Oranges" ┊        "store-3" ┊       "2017-May" ┊            0
"Granny Smith" ┊       "store-13" ┊       "2017-May" ┊            0
"Honeyscrisp Apples" ┊       "store-13" ┊       "2017-May" ┊            0
"Clementines" ┊       "store-13" ┊       "2017-May" ┊            0
"Blood Orange" ┊       "store-13" ┊       "2017-May" ┊            0
"Radishes" ┊        "store-3" ┊       "2017-May" ┊            0
"Beets" ┊        "store-3" ┊       "2017-May" ┊            0
"Navel Oranges" ┊       "store-13" ┊       "2017-May" ┊            0
"Pink Grapefruit" ┊       "store-13" ┊       "2017-May" ┊            0
"Honeyscrisp Apples" ┊        "store-4" ┊       "2017-May" ┊            0
"Navel Oranges" ┊        "store-4" ┊       "2017-May" ┊            0
"Blood Orange" ┊        "store-4" ┊       "2017-May" ┊            0
"Granny Smith" ┊        "store-4" ┊       "2017-May" ┊            0
"Beets" ┊       "store-13" ┊       "2017-May" ┊            0
"Ginger" ┊       "store-13" ┊       "2017-May" ┊            0

Displayed 30 out of 1296 result entries.```

It works! But, to see and validate the behavior of the slide we could zoom in on some positions.

Let's query our `Sales Units` for Blood Oranges (`sku-01`) sold at `store-1` in the months June and July in the years 2017, 2018, and 2019.

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
sku = {("sku-01")}:{Sku},
store = {("store-1")}:{Store},
months = {("2017-06"),("2017-07"),("2018-06"),("2018-07"),("2019-06"),("2019-07")}:{Month},
salesUnits = ( dice SalesUnits_U_TY_SKSTMO by sku and store and months )
) in (
salesUnits
)
// Exiting multi-line mode, now evaluating.

Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of int
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊        "store-1" ┊       "2017-Jun" ┊        17268
"Blood Orange" ┊        "store-1" ┊       "2017-Jul" ┊        13204
"Blood Orange" ┊        "store-1" ┊       "2018-Jun" ┊            0
"Blood Orange" ┊        "store-1" ┊       "2018-Jul" ┊            0
"Blood Orange" ┊        "store-1" ┊       "2019-Jun" ┊            0
"Blood Orange" ┊        "store-1" ┊       "2019-Jul" ┊            0

Displayed all 6 result entries.```

Now, apply our slide to the diced `salesUnits` to validate whether we indeed succeeded in time-shifting the `Sales Units` data.

```> :query
// Entering multi-line mode (ctrl-D to finish)
let (
sku = {("sku-01")}:{Sku},
store = {("store-1")}:{Store},
months = {("2017-06"),("2017-07"),("2018-06"),("2018-07"),("2019-06"),("2019-07")}:{Month},
salesUnits = ( dice SalesUnits_U_TY_SKSTMO by sku and store and months )
) in (
collect salesUnits by slide Calendar_Month_next_year
)
// Exiting multi-line mode, now evaluating.

Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of int
┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈
"Blood Orange" ┊        "store-1" ┊       "2018-Jun" ┊        17268
"Blood Orange" ┊        "store-1" ┊       "2018-Jul" ┊        13204
"Blood Orange" ┊        "store-1" ┊       "2019-Jun" ┊            0
"Blood Orange" ┊        "store-1" ┊       "2019-Jul" ┊            0

Displayed all 4 result entries.```

That looks good, the resulting measure indeed contains the values of last year's `Sales Units`! As an example, June 2018 now contains the `Sales Units` of June 2017.

Ok, time to configure our time-shifting business rules for our `LY` measures in the training application. Let's start with adding rules to calculate `SalesUnits_U_LY_SKSTMO`, `MarginPct_P_LY_SKSTMO` and `AvgSellingPrc_R_LY_SKSTMO`.

### Modify Me

Add the following logic to `<TRAINING_HOME>/src/rules/tutorial4.logic`:

```^modeler_config:metamodel:cubiql:metric_expr["SalesUnits_U_LY_SKSTMO"] = """
collect SalesUnits_U_TY_SKSTMO by slide Calendar_Month_next_year
""".

^modeler_config:metamodel:cubiql:metric_expr["MarginPct_P_LY_SKSTMO"] = """
collect MarginPct_P_TY_SKSTMO by slide Calendar_Month_next_year
""".

^modeler_config:metamodel:cubiql:metric_expr["AvgSellingPrc_R_LY_SKSTMO"] = """
collect AvgSellingPrc_R_TY_SKSTMO by slide Calendar_Month_next_year
""".```

### Build Me

Run the commands below:

```\$ make load-cubiql-rules
\$ make load-test-data```

You can verify your CubiQL rules using an Ad Hoc Canvas. For instance, in the screenshot below, we've configured the TY and LY measures to display next to each other. You can easily see that their values are offset by exactly one year (or 12 months):

The rules for the remaining two measures (`SalesRetail_R_LY_SKSTMO` and `COGS_R_LY_SKSTMO`) are the same as the rules for the TY version of those measures: they are calculated using the other `LY` measures (instead of the `TY` version). Try to add the rules to `<TRAINING_HOME>/src/rules/tutorial4.logic`. If you need a little bit of help, take a look at the section below.

### Modify Me

Add the following rules to `<TRAINING_HOME>/src/rules/tutorial4.logic`:

```^modeler_config:metamodel:cubiql:metric_expr["COGS_R_LY_SKSTMO"] = """
SalesRetail_R_LY_SKSTMO * (1d - MarginPct_P_LY_SKSTMO)
""".

^modeler_config:metamodel:cubiql:metric_expr["SalesRetail_R_LY_SKSTMO"] = """
SalesUnits_U_LY_SKSTMO as decimal * AvgSellingPrc_R_LY_SKSTMO
""".```

Notice how these rules are the same as the TY version of these rules, we just use the LY version of the measures within the calculations.

### Build Me

Run the commands below:

```\$ make load-cubiql-rules
\$ make load-test-data```

Validate via an Ad Hoc Canvas that your measures are calculated correctly.