KB4398: How to show data for the last common date in a selected subset of attributes in MicroStrategy Engine

• Strategy

How to show data for the last common date in a selected subset of attributes in MicroStrategy Engine

SYMPTOM:
This document takes as an example the following data model:

with the following data stored in the CITY_DATE fact table:

The user requires the report to show the balance for the last common date for all the cities within the countries selected.
If Italy, Spain and Argentina are selected, the user expects the following result, since the last common date between the three cities is March 30, 2000:

However, if only Italy and Spain are selected in the prompt, the expected result is as follows:

The data changes since the last common date between these cities, with a reported balance, is April 30, 2000.

ACTION:
To perform this type of analysis, create the following objects:

FILTER1: Prompt on Country -returns a set of countries.

METRIC1: Common Date - a nested aggregation metric that returns the last common date for all countries.

Definition:

Min(Max((Date + (Balance * 0))) {City+} ) {!City+} <>
The inner aggregation is defined as: Max((Date + (Balance * 0)) {City+}
DIMENSIONALITY: City (Filtering: Standard, Grouping: Standard)
The outer aggregation is defined as:
Min(…) {!City+} <>
DIMENSIONALITY:
City (Filtering: Standard, Grouping: None)
CONDITION:
Prompt on Country
The '(Balance * 0)' expression is added to the metric definition to
force the engine to query the fact table and not the lookup table (since
'Balance' is a fact) for the metric calculation.

This filter will generate the first and second passes of SQL shown below.

FILTER2: Date = Common Date - a filter that returns the rows for the dates that equal to the last common date.

Definition:

A custom metric qualification has to be created where the Common Date is equaled to the ID form for the date (Date@ID).

This filter will generate the third pass of SQL shown below. An MQ (Metric Qualification) table will be created.

METRIC2: Balance - the actual metric shown on the report.

Definition:


Sum(Balance) {~+}
There are no modifications needed for any parameter.

GRID:

FILTERS:
'Common Date' and 'Prompt on Country'
METRICS:
Balance
ATTRIBUTES:
City and Date

GRIDSQL

If the report is run and Spain and Argentina are selected, the following result appears:

FILTERS:
'Common Date' and 'Prompt on Country'
METRICS:
Balance
ATTRIBUTES:
City and Date

GRIDSQL

select a11.CITY_ID CITY_ID,
max((a11.date + (a11.BALANCE * 0))) WJXBFS1
into #ZZTJY00L903MD00
from CITY_DATE a11,
LU_CITY a12
where a11.CITY_ID = a12.CITY_ID
and a12.COUNTRY_ID in (3, 2)
group by a11.CITY_ID

select min(a11.WJXBFS1) COMMONDATE
into #ZZTJY00L903MD01
from #ZZTJY00L903MD00 a11,
LU_CITY a12
where a11.CITY_ID = a12.CITY_ID
and a12.COUNTRY_ID in (3, 2)

select distinct a12.CITY_ID CITY_ID,
a13.date date
into #ZZTJY00L903MQ02
from #ZZTJY00L903MD01 a11,
LU_CITY a12,
LU_DATE a13
where (a11.COMMONDATE = a13.date)

select a11.CITY_ID CITY_ID,
a13.CITY_DESC CITY_DESC,
a11.date date,
a11.BALANCE BALANCE
from CITY_DATE a11,
#ZZTJY00L903MQ02 a12,
LU_CITY a13
where a11.CITY_ID = a12.CITY_ID and
a11.date = a12.date and
a11.CITY_ID = a13.CITY_ID
and a13.COUNTRY_ID in (3, 2)

Comment

0 comments

Details

Knowledge Article

Published:

June 1, 2017

Last Updated:

June 1, 2017