The MicroStrategy provides more than 200 predefined functions for easier analysis when there are difficulties to achieve them or configured in the data model side… Generally i would advise the users to not to go for the usage of MicroStrategy functions –> as it would degrade report performance when we mainly work on a bigger enterprise for the clients… Can use for POC/Smaller Projects, where data is of small amount & performance is not the major problem…
Lets discuss today about one of the useful MicroStrategy function : Trend Function
The MicroStrategy Trend Function, available in MicroStrategy Desktop (//Schema Objects/Functions & Operators/Plug-in Packages/Statistical Functions), also relies on the Analytical engine for processing ( which is why i advise not to use for larger enterprise solutions )…
The trend function uses data recorded for certain time intervals to calculate and display the expected value of data for an unknown time period ( which is much like Data Mining )… This is most commonly used to predict future metric values based on historical data.
The report below shows an example of trend function. The report contains three conditional metrics : Jan 2009 Revenue, Feb 2009 Revenue and Mar 2009 Revenue. It also contains trend for April 2009 Revenue metric, which uses the following formula;
Trend( [Jan 2009 Revenue], [Feb 2009 Revenue], [Mar 2009
Revenue], 1.0 )
Revenue], 1.0 )
The SQL for the above report would be as follows;
select a13.REGION_ID REGION_ID,
sum((Case when a12.MONTH_ID in (200901)
then a11.TOT_DOLLAR_SALES else NULL end))
WJXBFS1,
max(( case when a12.Month_id in (200901)
then 1 else 0 end)) GODWFLAG1_1,
sum((Case when a12.MONTH_ID in (200902)
then a11.TOT_DOLLAR_SALES else NULL end))
WJXBFS2,
max(( case when a12.Month_id in (200902)
then 1 else 0 end)) GODWFLAG2_1,
sum((Case when a12.MONTH_ID in (200903)
then a11.TOT_DOLLAR_SALES else NULL end))
WJXBFS3,
max(( case when a12.Month_id in (200903)
then 1 else 0 end)) GODWFLAG3_1
into #ZZTD12AHM04MD000
from DAY_CTR_SLS a11
join
LU_DAY a12
on (a11.DAY_DATE=a12.DAY_DATE)
join LU_CALL_CTR a13
on (a11.CALL_CTR_ID=a13.CALL_CTR_ID)
where
(a12.MONTH_ID in (200901)
or a12.MONTH_ID in (200902)
or a12.MONTH_ID in (200903))
GROUP BY
a13.REGION_ID
select pa13.REGION_ID REGION_ID,
a14.REGION_NAME REGION_NAME0,
pa13.WJXBFS1,
pa13.WJXBFS2,
pa13.WJXBFS3
from
#ZZTD12AHM04MD000 pa13
join
LU_REGION a14
on ( pa13.REGION_ID=a14.REGION_ID)
where
(pa13.GODWFLAG1_1 = 1
and
pa13.GODWFLAG2_1 = 1
and
pa13.GODWFLAG3_1 = 1)
[Analytical SQL calculated by the Analytical engine :
select REGION_ID,
REGION_NAME0,
WJXBFS1,
WJXBFS2,
WJXBFS3,
Trend(WJXBFS1,WJXBFS2,WJXBFS3, 1.0)
from
[Previous Pass]
Hope it helps for the day!!!