Cumulative Calculation

Question

Cumulative Calculation

ttdeveloper

SSCrazy

Points: 2370
More actions
December 8, 2014 at 8:27 am

#292693

Good Day
I am quite a newbie to MDX and feeling my way through. I have created an SSAS Cube sourced off a SQL DW and have a requirement to report one specific metric - staffinservice.
Each Month, new staff join the company and I need to report figures for monthly, quarter, year and so on.
My Scenario
Month New Staff Total Staff
Oct 1 15
Nov 2 17
Dec 0 17
Q4 17
YTD 17
This is sort of a to date metric. The data I am sourcing off rolls up the monthly values just fine. The problem I have is that when it gets to any time period above month, instead of returning the todate figures, it is rolling up all the monthly totals (which is actually by design) and reporting 15+17+17 = 49 for Q4. The same applies for anything above the month. If I pulled up YTD staff count, as supposed to reporting 17, it's adding up all total staff values from Jan to current month.
Any helping on the MDX syntax to create this calculated member is appreciated.
The TSQL Definition of what I am looking is below and result sets attached
IF OBJECT_ID ('tempdb..#tmpLIS') > 0 DROP TABLE #tmpLIS
SELECT d.yearno, d.quarterno, d.monthno, s.segment_nm, SUM(f.Staffin)endStaff, SUM(BeginningStaff)BeginningStaff
INTO #tmpLIS
FROM FactVES f
inner join DimDate d
on d.MMYYYY = f.MonthYrKey
inner join DimSegment s
on s.Segment_ID = f.SegmentID
where d.YearNo = 2014
and s.Segment_NM = 'Marketing'
group by d.yearno, d.quarterno, d.monthno, s.segment_nm
order by d.QuarterNo
SELECT quarterno, MAX(BeginningStaff)BeginningStaff, MAX(endStaff)endStaff
from #tmplis
group by quarterno
order by quarterno
SELECT Yearno, MAX(BeginningStaff)BeginningStaff, MAX(endStaff)endStaff
from #tmplis
group by Yearno
order by Yearno
Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

This article is old (ie circa SQL 2000) but the concepts remain the same - you want to look into semi-additive measures, and likely 'closing balance' (or value). You could use this as a starting point to help you google (bing?) how to implement the same concept in 08/R2/12 whatever version you're running.

Steve.

ttdeveloper SSCrazy Points: 2370 More actions · Answer 2

First off Steve

Thanks for the quick response. "Closing balance" is defnitely the term I have been looking for and for the life of me couldnt put these words together. My Dev environment is BIDS 2008. Ill check the article and see if anything helps otherwise ill see what other help comes here in the form of MDX.

Thanks again.

ttdeveloper SSCrazy Points: 2370 More actions · Answer 3

Hey Steve

Thank you so much for pointing me in the right direction. It helps when you have the correct search term to look for.

Used the link below in my first google search and that helped me solve my problem

http://www.purplefrogsystems.com/blog/2008/04/semi-additive-measures-using-sql-server-standard/

Awesome help..