Tutorial 4  Basic Business Rules
Table of Contents
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/restorebase tutorial4 $ 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 measureservice repl uri ws://localhost:55183/modelertraining/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 nonnegative square root of x , for nonnegative 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 multiline mode (ctrlD to finish) let ( old = 200d, new = 220d, increase = new  old ) in ( increase / old * 100d ) // Exiting multiline 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 multiline mode (ctrlD to finish) let ( expr = 1 ) in ( 1.5d + expr as decimal ) // Exiting multiline mode, now evaluating. value of decimal ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ 2.5d Displayed all 1 result entries.
Some of CubiQL's builtin 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 multiline mode (ctrlD to finish) let ( expr = 4d ) in ( sqrt(expr) ) // Exiting multiline 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 multiline mode (ctrlD to finish) let ( expr = 4d ) in ( sqrt(expr as float) ) // Exiting multiline 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 builtin 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 uppercase

to_lower(x) 
Converts the characters of the string x to lowercase

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 straightforward:
SkuMarkdown_R_TY_SK
holds the markdown value in $ amount. We learn in this tutorial how this measure can be computed fromSkuMarkdownPct_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 readonly.

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 loadcubiqlrules $ make loadtestdata
With the first command, we load the business rules and then the second command is used to reimport 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 loadcubiqlrules $ make loadtestdata]
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 preconfigured
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:
 The formula is pretty straight forward and similar to the ones that you wrote before. It contains two operators (multiplication and subtraction).
 As the multiplication operator has precedence over the subtraction operator, we enforce different precedence (i.e. subtraction first) using parenthesis.

Notice the
d
suffix for the number1
? This suffix is needed, as arithmetic operators only apply to numeric values of the same type. Without the suffixd
,1
is interpreted as an integer which is a different type thanMarginPct_P_TY_SKSTMO
, a decimal. We, therefore, indicate that we want the decimal number 1, using1d
.
Build Me
Run the commands below to load the new rule:
$ make loadcubiqlrules $ make loadtestdata
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" ┊ "store4" ┊ "2017Jan" ┊ 7162.62d "Pink Grapefruit" ┊ "store4" ┊ "2017Jan" ┊ 9806.82d "Granny Smith" ┊ "store4" ┊ "2017Jan" ┊ 2715.51d "Honeyscrisp Apples" ┊ "store4" ┊ "2017Jan" ┊ 134575.65d "Beets" ┊ "store4" ┊ "2017Jan" ┊ 81942.3d "Clementines" ┊ "store4" ┊ "2017Jan" ┊ 119979.2d "Navel Oranges" ┊ "store4" ┊ "2017Jan" ┊ 155669.4d "Ginger" ┊ "store4" ┊ "2017Jan" ┊ 88929.29d "Radishes" ┊ "store4" ┊ "2017Jan" ┊ 9833.4d "Granny Smith" ┊ "store1" ┊ "2017Jan" ┊ 130802.76d "Ginger" ┊ "store1" ┊ "2017Jan" ┊ 104841.66d "Beets" ┊ "store1" ┊ "2017Jan" ┊ 54947.96d "Pink Grapefruit" ┊ "store1" ┊ "2017Jan" ┊ 33764.52d "Navel Oranges" ┊ "store1" ┊ "2017Jan" ┊ 80589.59d "Blood Orange" ┊ "store1" ┊ "2017Jan" ┊ 10407.65d "Radishes" ┊ "store1" ┊ "2017Jan" ┊ 38483.95d "Clementines" ┊ "store1" ┊ "2017Jan" ┊ 46678.04d "Ginger" ┊ "store3" ┊ "2017Jan" ┊ 85930.74d "Radishes" ┊ "store3" ┊ "2017Jan" ┊ 30419.2d "Pink Grapefruit" ┊ "store3" ┊ "2017Jan" ┊ 1183.6d "Granny Smith" ┊ "store3" ┊ "2017Jan" ┊ 117062.81d "Blood Orange" ┊ "store3" ┊ "2017Jan" ┊ 4775.76d "Honeyscrisp Apples" ┊ "store1" ┊ "2017Jan" ┊ 132459.88d "Clementines" ┊ "store3" ┊ "2017Jan" ┊ 146984.77d "Navel Oranges" ┊ "store3" ┊ "2017Jan" ┊ 10309.52d "Radishes" ┊ "store13" ┊ "2017Jan" ┊ 1039.6d "Clementines" ┊ "store13" ┊ "2017Jan" ┊ 139910.43d "Granny Smith" ┊ "store13" ┊ "2017Jan" ┊ 40495.95d "Ginger" ┊ "store13" ┊ "2017Jan" ┊ 83379.1d "Honeyscrisp Apples" ┊ "store3" ┊ "2017Jan" ┊ 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 loadcubiqlrules $ make loadtestdata
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,Bestselling 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 positiononly 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" ┊ "store1" ┊ 182069 "Blood Orange" ┊ "store1" ┊ 161776 "Granny Smith" ┊ "store1" ┊ 166024 "Honeyscrisp Apples" ┊ "store1" ┊ 186505 "Clementines" ┊ "store1" ┊ 187165 "Navel Oranges" ┊ "store1" ┊ 191069 "Pink Grapefruit" ┊ "store1" ┊ 197111 "Blood Orange" ┊ "store3" ┊ 187164 "Navel Oranges" ┊ "store3" ┊ 216927 "Honeyscrisp Apples" ┊ "store3" ┊ 238202 "Radishes" ┊ "store3" ┊ 167242 "Beets" ┊ "store1" ┊ 223097 "Ginger" ┊ "store1" ┊ 231389 "Pink Grapefruit" ┊ "store3" ┊ 182353 "Clementines" ┊ "store3" ┊ 226774 "Navel Oranges" ┊ "store13" ┊ 150655 "Pink Grapefruit" ┊ "store13" ┊ 177654 "Radishes" ┊ "store13" ┊ 199790 "Blood Orange" ┊ "store13" ┊ 228184 "Ginger" ┊ "store3" ┊ 238223 "Granny Smith" ┊ "store3" ┊ 174676 "Clementines" ┊ "store13" ┊ 242671 "Beets" ┊ "store3" ┊ 218075 "Pink Grapefruit" ┊ "store4" ┊ 163500 "Clementines" ┊ "store4" ┊ 200543 "Blood Orange" ┊ "store4" ┊ 183662 "Navel Oranges" ┊ "store4" ┊ 162993 "Granny Smith" ┊ "store13" ┊ 159407 "Honeyscrisp Apples" ┊ "store13" ┊ 185173 "Beets" ┊ "store13" ┊ 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" ┊ "store13" ┊ 150655 "Honeyscrisp Apples" ┊ "store13" ┊ 185173 "Beets" ┊ "store13" ┊ 183931 "Ginger" ┊ "store13" ┊ 166700 "Radishes" ┊ "store13" ┊ 199790 "Granny Smith" ┊ "store13" ┊ 159407 "Clementines" ┊ "store13" ┊ 242671 "Blood Orange" ┊ "store13" ┊ 228184 "Pink Grapefruit" ┊ "store13" ┊ 177654 "Beets" ┊ "store1" ┊ 223097 "Blood Orange" ┊ "store1" ┊ 161776 "Radishes" ┊ "store1" ┊ 182069 "Honeyscrisp Apples" ┊ "store1" ┊ 186505 "Clementines" ┊ "store1" ┊ 187165 "Navel Oranges" ┊ "store1" ┊ 191069 "Pink Grapefruit" ┊ "store1" ┊ 197111 "Granny Smith" ┊ "store1" ┊ 166024 "Blood Orange" ┊ "store4" ┊ 183662 "Pink Grapefruit" ┊ "store4" ┊ 163500 "Honeyscrisp Apples" ┊ "store4" ┊ 214527 "Beets" ┊ "store4" ┊ 237522 "Navel Oranges" ┊ "store4" ┊ 162993 "Ginger" ┊ "store1" ┊ 231389 "Granny Smith" ┊ "store4" ┊ 164780 "Clementines" ┊ "store4" ┊ 200543 "Pink Grapefruit" ┊ "store3" ┊ 182353 "Granny Smith" ┊ "store3" ┊ 174676 "Beets" ┊ "store3" ┊ 218075 "Blood Orange" ┊ "store3" ┊ 187164 "Ginger" ┊ "store4" ┊ 236888 Displayed 30 out of 36 result entries.
3.2. Computing Bestselling Store per SKU
Now that we know the total sales units per SKU and store, we can calculate
our bestselling 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 multiline mode (ctrlD to finish) let ( SalesUnits_U_SKST = ( total SalesUnits_U_TY_SKSTMO @ {Sku,Store} ) ) in ( SalesUnits_U_SKST ) // Exiting multiline mode, now evaluating. Product : Sku ┊ Location : Store ┊ value of int ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ "Clementines" ┊ "store1" ┊ 187165 "Beets" ┊ "store1" ┊ 223097 "Pink Grapefruit" ┊ "store1" ┊ 197111 "Blood Orange" ┊ "store1" ┊ 161776 "Honeycrisp Apples" ┊ "store1" ┊ 186505 "Ginger" ┊ "store1" ┊ 231389 "Granny Smith" ┊ "store1" ┊ 166024 "Beets" ┊ "store13" ┊ 183931 "Ginger" ┊ "store13" ┊ 166700 "Blood Orange" ┊ "store13" ┊ 228184 "Clementines" ┊ "store13" ┊ 242671 "Radishes" ┊ "store1" ┊ 182069 "Navel Oranges" ┊ "store1" ┊ 191069 "Granny Smith" ┊ "store13" ┊ 159407 "Honeycrisp Apples" ┊ "store13" ┊ 185173 "Ginger" ┊ "store4" ┊ 236888 "Granny Smith" ┊ "store4" ┊ 164780 "Clementines" ┊ "store4" ┊ 200543 "Beets" ┊ "store4" ┊ 237522 "Navel Oranges" ┊ "store13" ┊ 150655 "Pink Grapefruit" ┊ "store13" ┊ 177654 "Honeycrisp Apples" ┊ "store4" ┊ 214527 "Radishes" ┊ "store13" ┊ 199790 "Granny Smith" ┊ "store3" ┊ 174676 "Honeycrisp Apples" ┊ "store3" ┊ 238202 "Beets" ┊ "store3" ┊ 218075 "Ginger" ┊ "store3" ┊ 238223 "Pink Grapefruit" ┊ "store4" ┊ 163500 "Blood Orange" ┊ "store4" ┊ 183662 "Radishes" ┊ "store4" ┊ 184360 Displayed 30 out of 36 result entries.
Can you write a CubiQL expression that calculates the bestselling 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 multiline mode (ctrlD to finish) let ( SalesUnits_U_SKST = ( total SalesUnits_U_TY_SKSTMO @ {Sku,Store} ) ) in ( max SalesUnits_U_SKST @ { Sku } ) // Exiting multiline 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 multiline mode (ctrlD 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 multiline mode, now evaluating. Product : Sku ┊ Location : Store ┊ value of int ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ "Blood Orange" ┊ "store13" ┊ 228184 "Clementines" ┊ "store13" ┊ 242671 "Beets" ┊ "store4" ┊ 237522 "Radishes" ┊ "store13" ┊ 199790 "Pink Grapefruit" ┊ "store1" ┊ 197111 "Granny Smith" ┊ "store3" ┊ 174676 "Honeycrisp Apples" ┊ "store3" ┊ 238202 "Navel Oranges" ┊ "store3" ┊ 216927 "Ginger" ┊ "store3" ┊ 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 positiononly measure.
Build Me
Run the commands below:
$ make loadcubiqlrules $ make loadtestdata
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 ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ "2017Jan" ┊ "store1" ┊ "Honeyscrisp Apples" ┊ "+" "2017Jan" ┊ "store1" ┊ "Blood Orange" ┊ "+" "2017Jan" ┊ "store1" ┊ "Ginger" ┊ "+" "2017Jan" ┊ "store1" ┊ "Granny Smith" ┊ "+" "2017Jan" ┊ "store3" ┊ "Blood Orange" ┊ "+" "2017Jan" ┊ "store1" ┊ "Radishes" ┊ "+" "2017Jan" ┊ "store3" ┊ "Granny Smith" ┊ "+" "2017Jan" ┊ "store3" ┊ "Honeyscrisp Apples" ┊ "+" "2017Jan" ┊ "store1" ┊ "Clementines" ┊ "+" "2017Jan" ┊ "store1" ┊ "Beets" ┊ "+" "2017Jan" ┊ "store1" ┊ "Navel Oranges" ┊ "+" "2017Jan" ┊ "store1" ┊ "Pink Grapefruit" ┊ "+" "2017Jan" ┊ "store3" ┊ "Radishes" ┊ "+" "2017Jan" ┊ "store3" ┊ "Navel Oranges" ┊ "+" "2017Jan" ┊ "store13" ┊ "Honeyscrisp Apples" ┊ "+" "2017Jan" ┊ "store13" ┊ "Blood Orange" ┊ "+" "2017Jan" ┊ "store3" ┊ "Beets" ┊ "+" "2017Jan" ┊ "store3" ┊ "Ginger" ┊ "+" "2017Jan" ┊ "store3" ┊ "Pink Grapefruit" ┊ "+" "2017Jan" ┊ "store3" ┊ "Clementines" ┊ "+" "2017Jan" ┊ "store13" ┊ "Navel Oranges" ┊ "+" "2017Jan" ┊ "store13" ┊ "Pink Grapefruit" ┊ "+" "2017Jan" ┊ "store4" ┊ "Blood Orange" ┊ "+" "2017Jan" ┊ "store13" ┊ "Radishes" ┊ "+" "2017Jan" ┊ "store13" ┊ "Ginger" ┊ "+" "2017Jan" ┊ "store13" ┊ "Granny Smith" ┊ "+" "2017Jan" ┊ "store13" ┊ "Clementines" ┊ "+" "2017Jan" ┊ "store13" ┊ "Beets" ┊ "+" "2017Jan" ┊ "store4" ┊ "Pink Grapefruit" ┊ "+" "2017Jan" ┊ "store4" ┊ "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 ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ "2017Jan" ┊ "store1" ┊ "Honeyscrisp Apples" ┊ "" "2017Jan" ┊ "store1" ┊ "Blood Orange" ┊ "" "2017Jan" ┊ "store1" ┊ "Navel Oranges" ┊ "" "2017Jan" ┊ "store1" ┊ "Pink Grapefruit" ┊ "" "2017Jan" ┊ "store3" ┊ "Blood Orange" ┊ "" "2017Jan" ┊ "store1" ┊ "Radishes" ┊ "" "2017Jan" ┊ "store1" ┊ "Ginger" ┊ "" "2017Jan" ┊ "store1" ┊ "Granny Smith" ┊ "" "2017Jan" ┊ "store1" ┊ "Clementines" ┊ "" "2017Jan" ┊ "store1" ┊ "Beets" ┊ "" "2017Jan" ┊ "store3" ┊ "Pink Grapefruit" ┊ "" "2017Jan" ┊ "store3" ┊ "Clementines" ┊ "" "2017Jan" ┊ "store3" ┊ "Radishes" ┊ "" "2017Jan" ┊ "store3" ┊ "Navel Oranges" ┊ "" "2017Jan" ┊ "store3" ┊ "Granny Smith" ┊ "" "2017Jan" ┊ "store3" ┊ "Honeyscrisp Apples" ┊ "" "2017Jan" ┊ "store3" ┊ "Beets" ┊ "" "2017Jan" ┊ "store3" ┊ "Ginger" ┊ "" "2017Jan" ┊ "store13" ┊ "Clementines" ┊ "" "2017Jan" ┊ "store13" ┊ "Beets" ┊ "" "2017Jan" ┊ "store13" ┊ "Navel Oranges" ┊ "" "2017Jan" ┊ "store13" ┊ "Pink Grapefruit" ┊ "" "2017Jan" ┊ "store13" ┊ "Honeyscrisp Apples" ┊ "" "2017Jan" ┊ "store13" ┊ "Blood Orange" ┊ "" "2017Jan" ┊ "store13" ┊ "Ginger" ┊ "" "2017Jan" ┊ "store13" ┊ "Granny Smith" ┊ "" "2017Jan" ┊ "store4" ┊ "Beets" ┊ "" "2017Jan" ┊ "store4" ┊ "Ginger" ┊ "" "2017Jan" ┊ "store4" ┊ "Pink Grapefruit" ┊ "" "2017Jan" ┊ "store4" ┊ "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 leveltolevel 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 onedimensional intersection) to different positions in the same Month
level.
Luckily, Modeler generates slides for ordered levels such as Month
which we can use for timeshifting data.
In this case, we can use the autogenerated slide Calendar_Month_next
to obtain a measure in which the values at a given Month
are timeshifted 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" ┊ "store4" ┊ "2017Mar" ┊ 21364.2d "Beets" ┊ "store4" ┊ "2017Mar" ┊ 73527.74d "Pink Grapefruit" ┊ "store4" ┊ "2017Mar" ┊ 5654.5d "Ginger" ┊ "store4" ┊ "2017Mar" ┊ 14763.69d "Radishes" ┊ "store4" ┊ "2017Mar" ┊ 3044.36d "Pink Grapefruit" ┊ "store3" ┊ "2017Mar" ┊ 9459.99d "Granny Smith" ┊ "store3" ┊ "2017Mar" ┊ 119128.24d "Blood Orange" ┊ "store4" ┊ "2017Mar" ┊ 7916.04d "Honeyscrisp Apples" ┊ "store4" ┊ "2017Mar" ┊ 3414.4d "Clementines" ┊ "store4" ┊ "2017Mar" ┊ 212478.24d "Navel Oranges" ┊ "store4" ┊ "2017Mar" ┊ 1699.4d "Radishes" ┊ "store3" ┊ "2017Mar" ┊ 18063.66d "Clementines" ┊ "store3" ┊ "2017Mar" ┊ 11025.0d "Granny Smith" ┊ "store1" ┊ "2017Mar" ┊ 71111.94d "Ginger" ┊ "store3" ┊ "2017Mar" ┊ 20653.75d "Honeyscrisp Apples" ┊ "store3" ┊ "2017Mar" ┊ 29441.88d "Beets" ┊ "store3" ┊ "2017Mar" ┊ 57162.03d "Navel Oranges" ┊ "store3" ┊ "2017Mar" ┊ 61966.12d "Blood Orange" ┊ "store3" ┊ "2017Mar" ┊ 26591.45d "Clementines" ┊ "store1" ┊ "2017Mar" ┊ 162249.36d "Navel Oranges" ┊ "store1" ┊ "2017Mar" ┊ 148512.39d "Ginger" ┊ "store1" ┊ "2017Mar" ┊ 109370.8d "Radishes" ┊ "store1" ┊ "2017Mar" ┊ 25628.49d "Beets" ┊ "store1" ┊ "2017Mar" ┊ 50808.6d "Pink Grapefruit" ┊ "store1" ┊ "2017Mar" ┊ 63884.61d "Blood Orange" ┊ "store1" ┊ "2017Mar" ┊ 14242.89d "Honeyscrisp Apples" ┊ "store1" ┊ "2017Mar" ┊ 140985.6d "Navel Oranges" ┊ "store13" ┊ "2017Mar" ┊ 20968.64d "Blood Orange" ┊ "store13" ┊ "2017Mar" ┊ 26929.55d "Radishes" ┊ "store13" ┊ "2017Mar" ┊ 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 (sku01
) sold at store1
in the months June 2017 (201706
), July 2017 (201707
), August 2017 (201708
), September 2017 (201709
), and October 2017 (201710
).
> :query // Entering multiline mode (ctrlD to finish) let ( sales = ( dice SalesRetail_R_TY_SKSTMO by {("sku01")}:{Sku} and {("store1")}:{Store} and {("201706"),("201707"),("201708"),("201709"),("201710")}:{Month} ) ) in ( sales ) // Exiting multiline mode, now evaluating. Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ "Blood Orange" ┊ "store1" ┊ "2017Aug" ┊ 292.82d "Blood Orange" ┊ "store1" ┊ "2017Jul" ┊ 15976.84d "Blood Orange" ┊ "store1" ┊ "2017Oct" ┊ 35083.62d "Blood Orange" ┊ "store1" ┊ "2017Sep" ┊ 13268.08d "Blood Orange" ┊ "store1" ┊ "2017Jun" ┊ 19512.84d Displayed all 5 result entries.
Then, look at the results when you use the Calendar_Month_next
slide.
> :query // Entering multiline mode (ctrlD to finish) let ( sales = ( dice SalesRetail_R_TY_SKSTMO by {("sku01")}:{Sku} and {("store1")}:{Store} and {("201706"),("201707"),("201708"),("201709"),("201710")}:{Month} ) ) in ( collect sales by slide Calendar_Month_next ) // Exiting multiline mode, now evaluating. Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of decimal ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ "Blood Orange" ┊ "store1" ┊ "2017Nov" ┊ 35083.62d "Blood Orange" ┊ "store1" ┊ "2017Jul" ┊ 19512.84d "Blood Orange" ┊ "store1" ┊ "2017Oct" ┊ 13268.08d "Blood Orange" ┊ "store1" ┊ "2017Aug" ┊ 15976.84d "Blood Orange" ┊ "store1" ┊ "2017Sep" ┊ 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 autogenerated 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 multiline mode (ctrlD 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 multiline mode, now evaluating. Calendar : Month ┊ Location : Store ┊ Product : Sku ┊ value of string ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ "2017Feb" ┊ "store1" ┊ "Clementines" ┊ "+" "2017Feb" ┊ "store1" ┊ "Honeyscrisp Apples" ┊ "+" "2017Feb" ┊ "store1" ┊ "Ginger" ┊ "+" "2017Feb" ┊ "store1" ┊ "Blood Orange" ┊ "+" "2017Feb" ┊ "store13" ┊ "Radishes" ┊ "+" "2017Feb" ┊ "store13" ┊ "Clementines" ┊ "+" "2017Feb" ┊ "store1" ┊ "Navel Oranges" ┊ "+" "2017Feb" ┊ "store1" ┊ "Pink Grapefruit" ┊ "+" "2017Feb" ┊ "store3" ┊ "Granny Smith" ┊ "+" "2017Feb" ┊ "store3" ┊ "Navel Oranges" ┊ "+" "2017Apr" ┊ "store13" ┊ "Granny Smith" ┊ "+" "2017Feb" ┊ "store3" ┊ "Blood Orange" ┊ "+" "2017Feb" ┊ "store4" ┊ "Clementines" ┊ "+" "2017Feb" ┊ "store4" ┊ "Granny Smith" ┊ "+" "2017Feb" ┊ "store3" ┊ "Pink Grapefruit" ┊ "+" "2017Feb" ┊ "store4" ┊ "Blood Orange" ┊ "+" "2017Apr" ┊ "store1" ┊ "Beets" ┊ "+" "2017Apr" ┊ "store1" ┊ "Clementines" ┊ "+" "2017Apr" ┊ "store4" ┊ "Navel Oranges" ┊ "+" "2017Apr" ┊ "store1" ┊ "Radishes" ┊ "+" "2017Apr" ┊ "store13" ┊ "Blood Orange" ┊ "+" "2017Apr" ┊ "store13" ┊ "Clementines" ┊ "+" "2017Apr" ┊ "store13" ┊ "Radishes" ┊ "+" "2017Apr" ┊ "store13" ┊ "Ginger" ┊ "+" "2017Apr" ┊ "store3" ┊ "Blood Orange" ┊ "+" "2017Apr" ┊ "store3" ┊ "Honeyscrisp Apples" ┊ "+" "2017Apr" ┊ "store3" ┊ "Beets" ┊ "+" "2017Apr" ┊ "store3" ┊ "Ginger" ┊ "+" "2017Apr" ┊ "store4" ┊ "Granny Smith" ┊ "+" "2017Apr" ┊ "store4" ┊ "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 multiline mode (ctrlD 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 multiline mode, now evaluating. Calendar : Month ┊ Location : Store ┊ Product : Sku ┊ value of string ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ "2017Feb" ┊ "store1" ┊ "Ginger" ┊ { "+" } "2017Feb" ┊ "store1" ┊ "Granny Smith" ┊ { "" } "2017Feb" ┊ "store1" ┊ "Clementines" ┊ { "+" } "2017Feb" ┊ "store1" ┊ "Beets" ┊ { "" } "2017Feb" ┊ "store1" ┊ "Honeyscrisp Apples" ┊ { "+" } "2017Feb" ┊ "store1" ┊ "Blood Orange" ┊ { "+" } "2017Feb" ┊ "store3" ┊ "Granny Smith" ┊ { "+" } "2017Feb" ┊ "store3" ┊ "Honeyscrisp Apples" ┊ { "" } "2017Feb" ┊ "store3" ┊ "Beets" ┊ { "" } "2017Feb" ┊ "store3" ┊ "Ginger" ┊ { "" } "2017Feb" ┊ "store1" ┊ "Navel Oranges" ┊ { "+" } "2017Feb" ┊ "store1" ┊ "Pink Grapefruit" ┊ { "+" } "2017Feb" ┊ "store3" ┊ "Blood Orange" ┊ { "+" } "2017Feb" ┊ "store1" ┊ "Radishes" ┊ { "" } "2017Feb" ┊ "store13" ┊ "Honeyscrisp Apples" ┊ { "" } "2017Feb" ┊ "store13" ┊ "Blood Orange" ┊ { "" } "2017Feb" ┊ "store13" ┊ "Ginger" ┊ { "" } "2017Feb" ┊ "store13" ┊ "Granny Smith" ┊ { "" } "2017Feb" ┊ "store3" ┊ "Pink Grapefruit" ┊ { "+" } "2017Feb" ┊ "store3" ┊ "Clementines" ┊ { "" } "2017Feb" ┊ "store3" ┊ "Radishes" ┊ { "" } "2017Feb" ┊ "store3" ┊ "Navel Oranges" ┊ { "+" } "2017Feb" ┊ "store4" ┊ "Blood Orange" ┊ { "+" } "2017Feb" ┊ "store13" ┊ "Radishes" ┊ { "+" } "2017Feb" ┊ "store4" ┊ "Granny Smith" ┊ { "+" } "2017Feb" ┊ "store4" ┊ "Honeyscrisp Apples" ┊ { "" } "2017Feb" ┊ "store13" ┊ "Clementines" ┊ { "+" } "2017Feb" ┊ "store13" ┊ "Beets" ┊ { "" } "2017Feb" ┊ "store13" ┊ "Navel Oranges" ┊ { "" } "2017Feb" ┊ "store13" ┊ "Pink Grapefruit" ┊ { "" } Displayed 30 out of 468 result entries.
Hm, do you notice that the results are setvalued { "+" , .. }
instead of being singlevalued (e.g "+"
)?
This is caused by the union which is always considered setvalued, 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 singlevalued.
To recover the singlevalued result, we can add the {{ functional = true }}
annotation to the expression.
> :query // Entering multiline mode (ctrlD 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 multiline mode, now evaluating. Calendar : Month ┊ Location : Store ┊ Product : Sku ┊ value of string ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ "2017Feb" ┊ "store1" ┊ "Clementines" ┊ "+" "2017Feb" ┊ "store1" ┊ "Beets" ┊ "" "2017Feb" ┊ "store1" ┊ "Navel Oranges" ┊ "+" "2017Feb" ┊ "store1" ┊ "Pink Grapefruit" ┊ "+" "2017Feb" ┊ "store1" ┊ "Honeyscrisp Apples" ┊ "+" "2017Feb" ┊ "store1" ┊ "Blood Orange" ┊ "+" "2017Feb" ┊ "store1" ┊ "Ginger" ┊ "+" "2017Feb" ┊ "store1" ┊ "Granny Smith" ┊ "" "2017Feb" ┊ "store3" ┊ "Beets" ┊ "" "2017Feb" ┊ "store3" ┊ "Ginger" ┊ "" "2017Feb" ┊ "store3" ┊ "Pink Grapefruit" ┊ "+" "2017Feb" ┊ "store3" ┊ "Clementines" ┊ "" "2017Feb" ┊ "store3" ┊ "Blood Orange" ┊ "+" "2017Feb" ┊ "store1" ┊ "Radishes" ┊ "" "2017Feb" ┊ "store3" ┊ "Granny Smith" ┊ "+" "2017Feb" ┊ "store3" ┊ "Honeyscrisp Apples" ┊ "" "2017Feb" ┊ "store13" ┊ "Ginger" ┊ "" "2017Feb" ┊ "store13" ┊ "Granny Smith" ┊ "" "2017Feb" ┊ "store13" ┊ "Clementines" ┊ "+" "2017Feb" ┊ "store13" ┊ "Beets" ┊ "" "2017Feb" ┊ "store3" ┊ "Radishes" ┊ "" "2017Feb" ┊ "store3" ┊ "Navel Oranges" ┊ "+" "2017Feb" ┊ "store13" ┊ "Honeyscrisp Apples" ┊ "" "2017Feb" ┊ "store13" ┊ "Blood Orange" ┊ "" "2017Feb" ┊ "store4" ┊ "Granny Smith" ┊ "+" "2017Feb" ┊ "store4" ┊ "Honeyscrisp Apples" ┊ "" "2017Feb" ┊ "store4" ┊ "Beets" ┊ "" "2017Feb" ┊ "store4" ┊ "Ginger" ┊ "" "2017Feb" ┊ "store13" ┊ "Navel Oranges" ┊ "" "2017Feb" ┊ "store13" ┊ "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 loadcubiqlrules $ make loadtestdata
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. TimeShifting 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 timeshift 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 timeshift data using the slide Calendar_Month_next
.
Unfortunately for us, we can't use the autogenerated 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 betweenMonth
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 autogenerated 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 loadcubiqlrules $ make loadtestdata
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" ┊ "store1" ┊ "2017May" ┊ 0 "Blood Orange" ┊ "store1" ┊ "2017May" ┊ 0 "Beets" ┊ "store1" ┊ "2017May" ┊ 0 "Ginger" ┊ "store1" ┊ "2017May" ┊ 0 "Navel Oranges" ┊ "store1" ┊ "2017May" ┊ 0 "Pink Grapefruit" ┊ "store1" ┊ "2017May" ┊ 0 "Granny Smith" ┊ "store1" ┊ "2017May" ┊ 0 "Honeyscrisp Apples" ┊ "store1" ┊ "2017May" ┊ 0 "Blood Orange" ┊ "store3" ┊ "2017May" ┊ 0 "Granny Smith" ┊ "store3" ┊ "2017May" ┊ 0 "Ginger" ┊ "store3" ┊ "2017May" ┊ 0 "Clementines" ┊ "store3" ┊ "2017May" ┊ 0 "Pink Grapefruit" ┊ "store3" ┊ "2017May" ┊ 0 "Radishes" ┊ "store1" ┊ "2017May" ┊ 0 "Honeyscrisp Apples" ┊ "store3" ┊ "2017May" ┊ 0 "Navel Oranges" ┊ "store3" ┊ "2017May" ┊ 0 "Granny Smith" ┊ "store13" ┊ "2017May" ┊ 0 "Honeyscrisp Apples" ┊ "store13" ┊ "2017May" ┊ 0 "Clementines" ┊ "store13" ┊ "2017May" ┊ 0 "Blood Orange" ┊ "store13" ┊ "2017May" ┊ 0 "Radishes" ┊ "store3" ┊ "2017May" ┊ 0 "Beets" ┊ "store3" ┊ "2017May" ┊ 0 "Navel Oranges" ┊ "store13" ┊ "2017May" ┊ 0 "Pink Grapefruit" ┊ "store13" ┊ "2017May" ┊ 0 "Honeyscrisp Apples" ┊ "store4" ┊ "2017May" ┊ 0 "Navel Oranges" ┊ "store4" ┊ "2017May" ┊ 0 "Blood Orange" ┊ "store4" ┊ "2017May" ┊ 0 "Granny Smith" ┊ "store4" ┊ "2017May" ┊ 0 "Beets" ┊ "store13" ┊ "2017May" ┊ 0 "Ginger" ┊ "store13" ┊ "2017May" ┊ 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 (sku01
) sold at store1
in the months June and July in the years 2017, 2018, and 2019.
> :query // Entering multiline mode (ctrlD to finish) let ( sku = {("sku01")}:{Sku}, store = {("store1")}:{Store}, months = {("201706"),("201707"),("201806"),("201807"),("201906"),("201907")}:{Month}, salesUnits = ( dice SalesUnits_U_TY_SKSTMO by sku and store and months ) ) in ( salesUnits ) // Exiting multiline mode, now evaluating. Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of int ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ "Blood Orange" ┊ "store1" ┊ "2017Jun" ┊ 17268 "Blood Orange" ┊ "store1" ┊ "2017Jul" ┊ 13204 "Blood Orange" ┊ "store1" ┊ "2018Jun" ┊ 0 "Blood Orange" ┊ "store1" ┊ "2018Jul" ┊ 0 "Blood Orange" ┊ "store1" ┊ "2019Jun" ┊ 0 "Blood Orange" ┊ "store1" ┊ "2019Jul" ┊ 0 Displayed all 6 result entries.
Now, apply our slide to the diced salesUnits
to validate whether we indeed succeeded in timeshifting the Sales Units
data.
> :query // Entering multiline mode (ctrlD to finish) let ( sku = {("sku01")}:{Sku}, store = {("store1")}:{Store}, months = {("201706"),("201707"),("201806"),("201807"),("201906"),("201907")}:{Month}, salesUnits = ( dice SalesUnits_U_TY_SKSTMO by sku and store and months ) ) in ( collect salesUnits by slide Calendar_Month_next_year ) // Exiting multiline mode, now evaluating. Product : Sku ┊ Location : Store ┊ Calendar : Month ┊ value of int ┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈┈ "Blood Orange" ┊ "store1" ┊ "2018Jun" ┊ 17268 "Blood Orange" ┊ "store1" ┊ "2018Jul" ┊ 13204 "Blood Orange" ┊ "store1" ┊ "2019Jun" ┊ 0 "Blood Orange" ┊ "store1" ┊ "2019Jul" ┊ 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 timeshifting 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 loadcubiqlrules $ make loadtestdata
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 loadcubiqlrules $ make loadtestdata
Validate via an Ad Hoc Canvas that your measures are calculated correctly.