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.