Hi,
We got of count of employees from measures against a date dimension.
we need to get average count for a time period (ie..week,quarter,year ).
and the formula for avg employee count: (empl-count on firstday of period+empl-count on last dayof period)/2
Date EMPCount
for ex : 1-Nov-2005 2361
2-Nov-2005 2521
3-Nov-2005 2762
4-Nov-2005 2500
avg count for week in novemeber: 2361+2500/2.
Kindly let me know how we can do this in SSAS cube.
Thanks in advance
Raj
I can think of a couple of ways of doing this, I don't have a descent sample set to perfomance test against, I suspect the second method may be faster, specially at higher level as it will not have to evaluate a large set of days.
Method "a" gets the descendants of the current time member and grabs the first and last member of that set to average them.
create member measures.a as ((Head({descendants([Time].[Financial].CurrentMember,[Time].[Financial].[Financial Date]) as mths},1).item(0),Measures.Amount) + (TAIL(mths,1).item(0),Measures.Amount))/2
Method "b" uses 2 recursive functions to walk down the time hierarchy to grab the first and last member underneath the currentmember. The third calculation then simple adds the first 2 and divides by 2.
|||create member measures.bhead as iif([Time].Financial.CurrentMember.Level is [Time].[Financial].[Financial Date],Measures.Amount,([Time].Financial.CurrentMember.FirstChild,Measures.Measures.bHead))
create member measures.btail as iif([Time].Financial.CurrentMember.Level is [Time].[Financial].[Financial Date],Measures.Amount,([Time].Financial.CurrentMember.LastChild,Measures.Measures.bTail))
create member measures.b as (measures.bhead + measures.btail)/2
Hi,
I tried with method "b" it's showing "#Value!"
We have server created time Dimension is it because of that ?
or we are getting active employees count thro named query?
server created time dimension is "atrntime" and dimension attributes are Date,year,week,day of week,day of year.
please help
thanks
|||No, neither of those things should stop the query from working. I did not have access to Adventure Works when I posted the last sample, so I had to remove some client specific stuff from the sample I sent. Below is an actual working query that will run against the Adventure Works sample database.
WITH
member measures.DateHead as iif([Date].Fiscal.CurrentMember.Level is [Date].[Fiscal].[Date]
,[Measures].[Internet Order Count]
,([Date].Fiscal.CurrentMember.FirstChild,measures.DateHead)
)
member measures.DateTail as iif([Date].Fiscal.CurrentMember.Level is [Date].[Fiscal].[Date]
,[Measures].[Internet Order Count]
,([Date].Fiscal.CurrentMember.LastChild,measures.DateTail)
)
member measures.AvgOrderCnt as (measures.DateHead + measures.DateTail)/2
SELECT
{Measures.DateHead
,Measures.DateTail
,Measures.AvgOrderCnt
,Measures.[Internet Order Count]} ON COLUMNS
,[Date].Fiscal.Month.Members ON ROWS
FROM [Adventure Works]
If you are still having issues, you may find that displaying the results of the two underlying measures may help to diagnose any issues. If you are still unable to resolve the #value problem, try posting your calcuations and I (or someone else) may be able to spot the issue.
|||Hi,
Thanks for the answer.
I tried with this calculated measure
CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead
AS iif([AtrnTime].[drill].CurrentMember.Level is [AtrnTime].[drill].[Week]
,[Measures].[ActiveEmpl]
,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead)
);
and the output from cube browser was
but the expected answer for datehead measure was
2368 for week 25 and 2374 for week 26
and one more thing, i was not able to figure it out is total :2375 for wk25 and 2375 for week 26 (it's not the average also....)
We have sever created time dimension "AtrnTime" and "drill" is the herarchy defined as year-week-date
thanks
Raj
|||I think what you are getting is the distinct count for the week and I think what you are after for the DateHead measure is the count for the first day. By putting the Week Level/Attribute in the test for the IIF clause, you have effectively stopped the recursion there. Changing the level in the test for the iif clause should give you the result you are after.
CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead
AS iif([AtrnTime].[drill].CurrentMember.Level is [AtrnTime].[drill].[Date]
,[Measures].[ActiveEmpl]
,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead)
);
I could possibly have coded my example better to show what I was intending, by using the IsLeaf() function, maybe the following is a better way of coding this measure.
CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead
AS iif( IsLeaf([AtrnTime].[drill].CurrentMember)
,[Measures].[ActiveEmpl]
,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead)
);
This will make the measure recurse down until it hits the leaf level of the hierarchy.
|||Hi,
Yeah you were right, i was getting Distinct Count for ActiveEmpl measure and i am after getting count for the first day.
i tried executing bothe the MDX scripts. It works at the day level but when i aggregate to the week level i should get the count of first day in that week .. but i am getting blank in that place.
CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead1
AS iif( IsLeaf([AtrnTime].[drill].CurrentMember)
,[Measures].[ActiveEmpl]
,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead1)
);
CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead2
AS iif([AtrnTime].[drill].CurrentMember.Level is [AtrnTime].[drill].[date]
,[Measures].[ActiveEmpl]
,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead2)
);
and the output of that was
but when i aggregate to the week level datehead1 and datehead2 was blank as below.
datehead1/datehead2 should be 2368 for wk25 and 2374 for wk26
thanks in advance..
|||
Would I be right if I were to guess that your week starts on Sunday, which normally does not have any data? I'm guessing that members without data are probably what is causing the blanks here. There are probably a number of ways of dealing with this, we could nest another IIF clause to effectively "walk" along the siblings at the day level, looking for a non-empty one, but I don't think that would be terribly efficient.
We could grab all the siblings at the date level and return the first non-empty.
eg.
CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead1
AS iif( IsLeaf([AtrnTime].[drill].CurrentMember)
,HEAD(NONEMPTY([AtrnTime].[drill].CurrentMember.Siblings, {[Measures].[ActiveEmpl]}),1)
,([AtrnTime].[drill].CurrentMember.FirstChild,measures.DateHead1)
);
But if we have to deal with sets of members and finding non-empty children it might be better not to use recursion and to grab all the non-empty descendants of the time dimension.
CREATE MEMBER CURRENTCUBE.[MEASURES].DateHead1
AS HEAD(NONEMPTY(Descendants([AtrnTime].[drill].CurrentMember
,[AtrnTime].[drill].[Date]), {[Measures].[ActiveEmpl]}),1)
;
And if we are going down that path I would suggest looking into coding the whole thing into one measure so that you do not have to do the nonempty twice (once for the first day and once for the last day). You can do this by naming the non empty set and re-using it.
eg
CREATE MEMBER CURRENTCUBE.[MEASURES].Avg
AS (HEAD(
NONEMPTY(Descendants([AtrnTime].[drill].CurrentMember
,[AtrnTime].[drill].[Date]) * {[Measures].[ActiveEmpl]}) AS NonEmptySet
,1).Item(0)
+
TAIL( NonEmptySet
,1).Item(0))
/ 2;
;
yeah your guess was right ,,sunday was the starting day of the week, and the blank row was because of no data for that day..,, finally the avg query worked which takes head/tail of nonempty set.
Thanks a lot Darren.
No comments:
Post a Comment