MicroStrategy ONE
Compute Non-Agg Before/After OLAP Functions (For Example, Rank) Calculated in Analytical Engine
Compute Non-Agg Before/After OLAP Functions/Rank is an advanced property that is hidden by default. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties.
When reports contain calculations based on non-aggregation metrics, this property controls the order in which the non-aggregation and calculations are computed.
Levels at Which You Can Set This
Database instance, report, and template
Examples
Calculate Non-Aggregation Before Analytical (default)
Copy
select a12.YEAR_ID YEAR_ID,
sum(a11.TOT_SLS_QTY) WJXBFS1
from HARI_REGION_DIVISION a11
join HARI_LOOKUP_DAY a12
on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
where a11.CUR_TRN_DT)
in (select min(a11.CUR_TRN_DT)
from HARI_LOOKUP_DAY a11
group by a11.YEAR_ID))
group by a12.YEAR_ID
create table #ZZTIS00H5J7MQ000(
YEAR_ID DECIMAL(10, 0))
[Placeholder for an analytical SQL]
select a12.YEAR_ID YEAR_ID,
max(a13.YEAR_DESC) YEAR_DESC,
sum(a11.TOT_SLS_QTY) TSQDIMYEARNA
from HARI_REGION_DIVISION a11
join HARI_LOOKUP_DAY a12
on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
join #ZZTIS00H5J7MQ000 pa1
on (a12.YEAR_ID = pa1.YEAR_ID)
join HARI_LOOKUP_YEAR a13
on (a12.YEAR_ID = a13.YEAR_ID)
where ((a11.CUR_TRN_DT)
in (select min(a15.CUR_TRN_DT)
from #ZZTIS00H5J7MQ000 pa1
join HARI_LOOKUP_DAY a15
on (pa1.YEAR_ID = a15.YEAR_ID)
group by pa1.YEAR_ID))
group by a12.YEAR_ID
Calculate Non-Aggregation After Analytical
Copy
select a11.CUR_TRN_DT CUR_TRN_DT,
a12.YEAR_ID YEAR_ID,
sum(a11.TOT_SLS_QTY) WJXBFS1
from HARI_REGION_DIVISION a11
join HARI_LOOKUP_DAY a12
on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
group by a11.CUR_TRN_DT,
a12.YEAR_ID
create table #ZZTIS00H5J8NB000(
CUR_TRN_DT DATETIME,
YEAR_ID DECIMAL(10, 0),
WJXBFS1 FLOAT)
[Placeholder for an analytical SQL]
insert into #ZZTIS00H5J8NB000 values (CONVERT
(datetime, '1993-12-01 00:00:00', 120), 1993,
44)
[The rest of the INSERT statements have been omitted from display].
Copy
select distinct pa1.YEAR_ID YEAR_ID,
pa1.WJXBFS1 WJXBFS1
from #ZZTIS00H5J8NB000 pa1
where ((pa1.CUR_TRN_DT)
in (select min(c11.CUR_TRN_DT)
from HARI_LOOKUP_DAY c11
group by c11.YEAR_ID))
create table #ZZTIS00H5J8MQ001(
YEAR_ID DECIMAL(10, 0),
WJXBFS1 FLOAT)
[Placeholder for an analytical SQL]
select a12.YEAR_ID YEAR_ID,
max(a13.YEAR_DESC) YEAR_DESC,
sum(a11.TOT_SLS_QTY) TSQDIMYEARNA
from HARI_REGION_DIVISION a11
join HARI_LOOKUP_DAY a12
on (a11.CUR_TRN_DT = a12.CUR_TRN_DT)
join #ZZTIS00H5J8MQ001 pa2
on (a12.YEAR_ID = pa2.YEAR_ID)
join HARI_LOOKUP_YEAR a13
on (a12.YEAR_ID = a13.YEAR_ID)
where ((a11.CUR_TRN_DT)
in (select min(a15.CUR_TRN_DT)
from #ZZTIS00H5J8MQ001 pa2
join HARI_LOOKUP_DAY a15
on (pa2.YEAR_ID = a15.YEAR_ID)
group by pa2.YEAR_ID))
group by a12.YEAR_ID