Last year Quarter / This year quarter data comparison

[MSTRST]

All,

I need to create a report for quarter to quarter data(revenue) comparison.

eg.
Qtr-> Q1-03 Q1-04 Q2-03 Q2-04 Q3-03 Q3-04
Product Revenue Variation
p1 $100 $125 ($25) $75 $57 ($18) $40 $50
P2 .....

Product/ year (quarter ) are attributes and revenue is a metric.
Any help/ideas are greatly appreciated.
7.5.1 MSTR Desktop version.

Thanks,

 

[mstrNate]

Have you tried creating transformation tables (or views) in the database and transformation schema objects which point to those tables in MicroStrategy? You could then use those transformation objects in various metrics, like your Revenue metric.

On a report, you would then place the Revenue metric and your new "Revenue LY" metric together. If you placed the attribute Quarter on the report, it would work just like what you were looking for.

If you can't create database objects and then schema objects in MicroStrategy, you could instead create filters within MicroStrategy which mimic the transformation logic as well.

hth,
Nate

 

[MSTRST]

Happy New Year to All

Thanks! MSTRNate

Can you please elaborate me on filter creation to mimic the transformation?
I created dynamic filters and am able to display the Revenue for corresponding years and quarters, but not able to display the revenue Varation between Q101 - Q201 , Q102-Q202...

Any ideas ? how to achieve this?


Thanks!!!

 

[mstrNate]

If you've got the filters getting your data as desired, then you can just add them to metrics. Create two metrics, one with a filter for Q101 (call it M1) and one with a filter for Q201 (call it M2). Then create a third metric called a compound metric that gets the variance between quarters. Define it as M1 - M2. If I understand correctly, that should work for you.

As for the transformation filters, you can create them using the "advanced qualification" option seen when creating a new filter. In there you can build filters using the Apply functions that MicroStrategy supplies. These functions allow you to pass database specific sql with parameters through a MicroStrategy wrapper, sort of. You would go through the same process as above to get a variance, by creating multiple metrics with the filters needed and then using compound metrics that bring them
together.

e.g. To get last week last year, you could use code similar to the following.

ApplyComparison ("#0 in (SELECT MAX(WK_IDNT)FROM TIME_DAY_DM WHERE DAY_DT < (SYSDATE -
371))", Week@ID)

 

[MSTRST]

Nate,

Thanks! for your response.

I see what you are saying, I tried that earlier.. i feel the M1, M2, M3 concept works fine only if it is like for Q101 - Q102 comparison but in my report i need

Q101 Q201 Varience Q201 Q202 Varience Q301 Q302 varience..
?

Appreciate your time to respond.

 

[mstrNate]

I might not understand what you're asking for. If you put quarter on the report, with those three metrics, you should be able to get the variance for all the quarters that show up on your report. MicroStrategy should repeat the three metrics for each quarter (with the correct values for each quarter - not repeating one set of values only). Building percent change metrics is common with the tool, and I think that's what you're asking for. If you've already tried it, post the sql that generates from your report. I'll see if I can understand what you're doing. - Nate

 

[MSTRST]

Nate,

This is the SQL for Year-Year,

I have used apply functions in the metric condition.

select nvl(a13.REVENUE_AREA_ID,999) REVENUE_AREA_ID,
a13.REVENUE_AREA_DESC REVENUE_AREA_DESC,
a11.NETWORK_KEY NETWORK_KEY,
a11.MARKET_KEY MARKET_KEY,
sum(a11.REVENUE) WJXBFS1
from DWH.INVOICE_REVENUE_FACT a11,
DWH.INVOICE_EFFECTIVE_DAY_DIM a12,
DWH.REVENUE_AREA_DIM a13
where a11.INVOICE_EFFECTIVE_DAY_KEY = a12.INVOICE_EFFECTIVE_DAY_KEY and
a11.REVENUE_AREA_KEY = a13.REVENUE_AREA_KEY
and a12.CALENDAR_YEAR = to_number(to_char(sysdate,'yyyy')-1)
group by nvl(a13.REVENUE_AREA_ID,999),
a13.REVENUE_AREA_DESC,
a11.NETWORK_KEY,
a11.MARKET_KEY
----
select nvl(a13.REVENUE_AREA_ID,999) REVENUE_AREA_ID,
a13.REVENUE_AREA_DESC REVENUE_AREA_DESC,
a11.NETWORK_KEY NETWORK_KEY,
a11.MARKET_KEY MARKET_KEY,
sum(a11.REVENUE) WJXBFS1
from DWH.INVOICE_REVENUE_FACT a11,
DWH.INVOICE_EFFECTIVE_DAY_DIM a12,
DWH.REVENUE_AREA_DIM a13
where a11.INVOICE_EFFECTIVE_DAY_KEY = a12.INVOICE_EFFECTIVE_DAY_KEY and
a11.REVENUE_AREA_KEY = a13.REVENUE_AREA_KEY
and a12.CALENDAR_YEAR = to_number(to_char(sysdate,'yyyy'))
group by nvl(a13.REVENUE_AREA_ID,999),
a13.REVENUE_AREA_DESC,
a11.NETWORK_KEY,
a11.MARKET_KEY
--- function
select distinct pa4.MARKET_KEY MARKET_KEY,
a11.MARKET_DESC MARKET_DESC,
pa4.REVENUE_AREA_ID REVENUE_AREA_ID,
pa4.REVENUE_AREA_DESC REVENUE_AREA_DESC,
pa4.NETWORK_KEY NETWORK_KEY,
a12.NET_NAME NET_NAME,
pa1.WJXBFS1 WJXBFS1,
pa2.WJXBFS1 WJXBFS2,
(NVL(pa2.WJXBFS1, 0) - NVL(pa1.WJXBFS1, 0)) WJXBFS3
from ZZT6C010PDBOD003 pa4,
ZZT6C010PDBMD000 pa1,
ZZT6C010PDBMD001 pa2,
DWH.MARKET_DIM a11,
DWH.NETWORK_DIM a12
where pa4.MARKET_KEY = pa1.MARKET_KEY (+) and
pa4.NETWORK_KEY = pa1.NETWORK_KEY (+) and
pa4.REVENUE_AREA_ID = pa1.REVENUE_AREA_ID (+) and
pa4.MARKET_KEY = pa2.MARKET_KEY (+) and
pa4.NETWORK_KEY = pa2.NETWORK_KEY (+) and
pa4.REVENUE_AREA_ID = pa2.REVENUE_AREA_ID (+) and
pa4.MARKET_KEY = a11.MARKET_KEY and
pa4.NETWORK_KEY = a12.NETWORK_KEY

******************

 

[mstrNate]

Sorry for the delay. Can you try adding the quarter attribute to your report, filtering for the current years completed quarters (e.g. Q1-Q3 2004) and send the sql again? Also, can you send the description of your INVOICE_EFFECTIVE_DAY_DIM table so that I can see what columns you have in it? Thanks. - Nate

 

[MSTRST]

Nate,

Thanks for your responses.

Here is the SQL where i have the Qtr Attribute on the report with Rev. metric and a report filter Year between this year and last year.
In this it displays revenue values, but how do i get comparisons?


Here is the Description for Invoice_effective_Day_dim

INVOICE_EFFECTIVE_DAY_KEY, DAY_DATE,
DAY_NUM_IN_WEEK, DAY_WEEK_END_IND, CALENDAR_DAY_NUM_IN_YEAR, CALENDAR_WEEK_KEY,
CALENDAR_WEEK_NUM, CALENDAR_MONTH_KEY, CALENDAR_MONTH_NUM, CALENDAR_MONTH_NAME, CALENDAR_QTR_KEY, CALENDAR_QTR_NUM, CALENDAR_HALF_KEY, CALENDAR_HALF, CALENDAR_YEAR_KEY, CALENDAR_YEAR, BRDCST_DAY_NUM_IN_YEAR, BRDCST_WEEK_KEY,
BRDCST_WEEK_NUM, BRDCST_MONTH_KEY, BRDCST_MONTH_NUM, BRDCST_MONTH_NAME, BRDCST_QTR_KEY, BRDCST_QTR_NUM,
BRDCST_HALF_KEY, BRDCST_HALF, BRDCST_YEAR_KEY, BDCST_YEAR,
HOLIDAY, HOLIDAY_IND, COMPETITIVE_PRESSURE_IND, COMPETITIVE_PRESSURE_EVENT, MONTH_CLOSED_LIABILITY,
DAY_OVERLAP_IND, CREATE_TS, UPDATE_TS, END_TS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL with Qtr Attribute and Year b/n current and last year Report filter.

select a11.MARKET_KEY MARKET_KEY,
a14.MARKET_DESC MARKET_DESC,
nvl(a13.REVENUE_AREA_ID,999) REVENUE_AREA_ID,
a13.REVENUE_AREA_DESC REVENUE_AREA_DESC,
a11.NETWORK_KEY NETWORK_KEY,
a15.NET_NAME NET_NAME,
a12.CALENDAR_QTR_KEY CALENDAR_QTR_KEY,
a12.CALENDAR_QTR_NUM||decode(a12.CALENDAR_QTR_NUM,null,'N/A',decode(a12.CALENDAR_QTR_NUM,1,'st',decode(a12.CALENDAR_QTR_NUM,2,'nd',decode(a12.CALENDAR_QTR_NUM,3,'rd','th')))||' Qtr '||a12.CALENDAR_YEAR) CustCol,
a12.CALENDAR_YEAR_KEY CALENDAR_YEAR_KEY,
a12.CALENDAR_YEAR CALENDAR_YEAR,
sum(a11.REVENUE) WJXBFS1
from DWH.INVOICE_REVENUE_FACT a11,
DWH.INVOICE_EFFECTIVE_DAY_DIM a12,
DWH.REVENUE_AREA_DIM a13,
DWH.MARKET_DIM a14,
DWH.NETWORK_DIM a15
where a11.INVOICE_EFFECTIVE_DAY_KEY = a12.INVOICE_EFFECTIVE_DAY_KEY and
a11.REVENUE_AREA_KEY = a13.REVENUE_AREA_KEY and
a11.MARKET_KEY = a14.MARKET_KEY and
a11.NETWORK_KEY = a15.NETWORK_KEY
and a12.CALENDAR_YEAR_KEY between to_number(to_char(sysdate,'yyyy')-1) and to_number(to_char(sysdate,'yyyy'))
group by a11.MARKET_KEY,
a14.MARKET_DESC,
nvl(a13.REVENUE_AREA_ID,999),
a13.REVENUE_AREA_DESC,
a11.NETWORK_KEY,
a15.NET_NAME,
a12.CALENDAR_QTR_KEY,
a12.CALENDAR_QTR_NUM||decode(a12.CALENDAR_QTR_NUM,null,'N/A',decode(a12.CALENDAR_QTR_NUM,1,'st',decode(a12.CALENDAR_QTR_NUM,2,'nd',decode(a12.CALENDAR_QTR_NUM,3,'rd','th')))||' Qtr '||a12.CALENDAR_YEAR),
a12.CALENDAR_YEAR_KEY,
a12.CALENDAR_YEAR

 

[mstrNate]

Hmmm...ok. I see the problem now. Sorry, I was confusing filters for year to date transformations with the type of transformation you're asking for, which is transforming this year to last year. I don't know of a way right now to do that type of transformation in a filter. I would suggest building a transformation schema object to do it. There are a couple of ways you can do that.

1. You can build a table or view in the database which contains two columns, calendar_qtr_key (e.g. 20011 is a value), and ly_calendar_qtr_key (20001 would be the corresponding value). Then you build the transformation and map in the respective columns. You can build these tables/views for each level of your time dimension (e.g. day, week, month, quarter…).

2. You could also do it independent of the database, if your keys will allow it. If you are using true surrogate keys with no meaning whatsoever (just some sort of sequence key), then you might not be able to do this. But, if you're using somewhat meaningful key values in your time dimension, like 20011 or 200101 to signify the first quarter of 2001, then you could do this all in MicroStrategy. Which is cool in case you don't have access to the database.

For example, you could build a transformation with a member attribute "calendar_qtr_key" and member expression for that attribute, of "[calendar_qtr_key]-10" if your values are 20011, or "calendar_qtr_key]-100" if your values are like 200101.

You probably would want to add multiple members to this “Last Year” transformation. For example, I have a transformation called "Last Year" which has Day, Week, Month, Quarter, and Year attributes all in it. That way, I don't have to build lots of separate transformations and thus metrics. After you’ve got your “Last Year” transformation, put it on a metric like revenue and call it Revenue LY (or whatever you want to). If you put that metric on a report, you’re then prepared to handle filtering at the different levels of time within the report.

I hope that makes sense.

Nate

 

[MSTRST]

Nate,

Thanks! a lot will work on it and let you know how it goes.

Thanks!!

 

[sunnyphoenixier]

Hi MSTRST,

I believe Transformation is the best answer to this question.

1. Create a transfermation on Year to Previous Year. (however your do it, there are many ways.)

2. Creat one metric called This Year Sales as normal.

3. Creat another metric called Last Year Sales with transformation.

4. Creat one more metric called Variance defined as This Year Sales - Last Year Sales.

We just implemented this. It worked for us.

Let me know,

Phoenixier

 

[MSTRST]

Nate/Phoenixer,

Thanks!! for your help.
Phoenixer, i got it working with Nates idea to creat transformation with year ,Qtr,Month in the transformation so it will be useful in drilling from year to qtr or month or various levels.

Nate- Thanks!again.