EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

KB14580: How to create a metric that calculates the Running Product of a certain set of values in MicroStrategy Developer 9.x and 10.x

Stefan Zepeda

Salesforce Solutions Architect • Strategy

How to create a metric that calculates the Running Product of a certain set of values in MicroStrategy Developer 9.x and 10.x

Users may want to create a metric able to calculate the running product of a certain set of values, as shown in the table below:
 

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

select a11.MONTH_ID MONTH_ID,
  sum(a11.TOT_COST) WJXBFS1
from MNTH_CATEGORY_SLS a11
where a11.MONTH_ID in (200001, 200002, 200003)
group by a11.MONTH_ID
into temp MD000 with no log

select a11.MONTH_ID MONTH_ID,
  sum(a11.UNITS_RECEIVED) WJXBFS1
from INVENTORY_ORDERS a11
where a11.MONTH_ID in (200001, 200002)
group by a11.MONTH_ID
into temp MD001 with no log

select pa2.MONTH_ID MONTH_ID,
  a11.MONTH_DESC MONTH_DESC,
  (NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) WJXBFS1
from MD001 pa2, outer
  MD000 pa1,
  LU_MONTH a11
where pa2.MONTH_ID = pa1.MONTH_ID and
  pa2.MONTH_ID = a11.MONTH_ID
and ((NVL(pa1.WJXBFS1, 0) + NVL(pa2.WJXBFS1, 0)) > 600000.0)
M1Running product (M1)aaba*bca*b*cda*b*c*dea*b*c*d*e

However, Strategy does not include a ready-made function for it. In order to create such a metric, it is necessary to utilize a combination of various functions that use logarithms and existing OLAP functions.
 
To create the metric Running product (M1) shown above, create a new metric to have the following definition:
 
EXP ( RunningSum ( LN ( M1) ) )
Applying a Natural Logarithm (LN) to the metric will convert the values to the logarithmic scale. Math theory specifies that a regular multiplication is equivalent to a Sum in the logarithmic realm. Therefore, using a RunningSum* on logarithmic values will result on a product when converting the values back to the real realm by using the exponential function.
 
Below is an example of a report using the Strategy Tutorial project. Metric 'A' is the base metric. The subsequent metrics illustrate the values for each metric definition that corresponds to the metric name. The running product metric is the final metric named 'Exp(RunningSum(LN(A))).'
 

ka02R000000kVazQAE_0EM440000002FPG.jpeg

 
NOTE: Make sure that the 'sort by' and 'break by' parameters in the Running Sum function are set correctly for the specific needs of the report.
 

Comment

0 comments

Details

Knowledge Article

Published:

May 31, 2017

Last Updated:

May 31, 2017