comparing this period to last QTD, YTD, MTD, etc..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

comparing this period to last QTD, YTD, MTD, etc..

Author

Topic

rpatel18
Starting Member

9 Posts

Posted - 2012-01-31 : 14:26:00

I'm trying to calculate the change in sales over various "last year" time frames. Such as, if I'm looking at this quarters sales, I want to compare them to last full year sales total, last years quarter sales total.

If i'm looking at this months sales, I want to compare them to last full year sales total, last years quarter sales total, and last years months sales total.

I'm using this MDX (don't know if its the best equation as I am only a week into OLAP, MDX, and SSAS) which works except when I drill down into the month level

([Measures].[Ext Sell Price], [Period to View].[PeriodHierarchy].CurrentMember)

-

(([Measures].[Ext Sell Price], ParallelPeriod([Period to View].[PeriodHierarchy].[CAL QTR ID],1))+

([Measures].[Ext Sell Price], ParallelPeriod([Period to View].[PeriodHierarchy].[CAL QTR ID],2))+

([Measures].[Ext Sell Price], ParallelPeriod([Period to View].[PeriodHierarchy].[CAL QTR ID],3))+

([Measures].[Ext Sell Price], ParallelPeriod([Period to View].[PeriodHierarchy].[CAL QTR ID],4)))

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-02-01 : 01:56:12

You gonna need to test what level you are currently working with, that will effect the bottom part of your calculated member.
Something along the lines of :
IIF
(
[Period to View].[PeriodHierarchy].Level.Name = "Year",
([Measures].[Ext Sell Price], [Period to View].[PeriodHierarchy].CurrentMember)
-
(([Measures].[Ext Sell Price], ParallelPeriod([Period to View].[PeriodHierarchy].[CAL QTR ID],1))+
([Measures].[Ext Sell Price], ParallelPeriod([Period to View].[PeriodHierarchy].[CAL QTR ID],2))+
([Measures].[Ext Sell Price], ParallelPeriod([Period to View].[PeriodHierarchy].[CAL QTR ID],3))+
([Measures].[Ext Sell Price], ParallelPeriod([Period to View].[PeriodHierarchy].[CAL QTR ID],4)))
,
IIF
(
[Period to View].[PeriodHierarchy].Level.Name = "Quarter"
,
--Code for calculation when true
,
--Code for calculation when false

)
)

Duane.

rpatel18
Starting Member

9 Posts

Posted - 2012-02-01 : 22:50:59

Hey Duane, thanks for the reply....Here is what I ended up using as my code, I think it works the same way except its comparing static last year values such as against last full year or last period or last ytd.
Here is my code:

----------------------------------------------------------------
CALCULATE;

CREATE MEMBER CURRENTCUBE.[Measures].vs_Full_Ly_Sales
AS ([Measures].[Sales], [Period to View].[CAL YEAR ID].currentmember.Prevmember),
FORMAT_STRING = "$#,#",
NON_EMPTY_BEHAVIOR = { [Sales] },
VISIBLE = 1;

CREATE MEMBER CURRENTCUBE.[Measures].vs_Ly_Qtr_Sales
AS ([Measures].[Sales], ParallelPeriod([Period to View].[PeriodHierarchy].[CAL YEAR ID],1)),
FORMAT_STRING = "$#,#",
NON_EMPTY_BEHAVIOR = { [Sales] },
VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[Measures].vs_Ly_YTD_Sales
AS SUM(YTD(ParallelPeriod([Period to View].[PeriodHierarchy].[CAL YEAR ID]
, 1
, [Period to View].[PeriodHierarchy].CurrentMember))
, [Measures].[Sales]),
FORMAT_STRING = "$#,#",
NON_EMPTY_BEHAVIOR = { [Sales] },
VISIBLE = 1;

----------------------------------------------------------

now, I do want to expand on this though. My next step would be to calculate repeat sales at a certain "match" level or I guess hierarchy?
I'm assuming I would need to make measures "vs_Full_Ly_Repeat_Sales", "vs_Ly_Qtr_Repeat_Sales" and so on. But how would I tell it to sum only sales that match at the Business --> Country --> CustID --> ProductID level for the last period? I have a hierarchy at the (highest to lowest) Business --> Country --> CustID --> ProductID level and I think I need to use that?

rpatel18
Starting Member

9 Posts

Posted - 2012-02-02 : 10:27:15

ok...so now I have this which works at the productID level,

CREATE MEMBER CURRENTCUBE.[Measures].[Full_Ly_Repeat_Sales]
AS NULL,
FORMAT_STRING = "$#,#",
VISIBLE= 1;

Scope ([ProductHR].[Hierarchy].[Product ID].members);
[Measures].[Full_Ly_Repeat_Sales] =([Measures].[Sales], [Period to View].[CAL YEAR ID].currentmember.Prevmember);
End Scope;

but now how do I sum those as I move up my hierarchy now?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources