Help...

I have a calculated member that I use to calculate rolling total ( sum) of
sales numbers.

Here is what I use.

Iif([Time].CurrentMember.level is [Time].[Week Name],
([Time].currentmember.lastchild, [measures].[cumulative]),
Max (descendants ([Time].currentmember, [Time]. [Day Name]),
[Measures].[Cumulative]))

TIme dimension has this 3 levels- Month, week, day. This function works
fine except when there are negative numbers in there.
Look at the data below and you will see that total for week 1 is fine.
However, Total for Month1 is not right.

Can anyone please help me with this.

Thank you so much for your help.


MOnth1 Week 1 Day1 40,862.76 40,862.76
Day2 - 4,908.98 35,953.78
Day3 - 1,981.43 33,972.36
Day4 - 1,601.47 32,370.88
Day5 - 933.34 31,437.54
Day6 .0 31,437.54

Total 31,437.54 31,437.54
Week 2 - 4,000.00 27,437.54
Week 3 4,000.00 31,437.54

Total 24,481.0 40,862.76

Looks like the logic at the week versus month levels is inconsistent.
Would something simpler like this work?

[Time].CurrentMember, [Time]. [Day Name])).Item(0),
[Measures].[Cumulative])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hey Deepak,

Thanks for your help. That does help.

One problem though. I don't get anything at ALL level on the top. How can I
get the
Sum of ALL month's total at ALL level for Time.

Thanks again.

amitJ

"Deepak Puri" wrote:

Not sure how your [Measures].[Cumulative] works - is it a calculated
measure? Right now, this expression returns [Measures].[Cumulative] for
the last day, but maybe that's empty, and some tweaking is necessary...


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hey Deepak,

What I meant was,
Month1 works perfect now. Total for Month2 (which is still open and has 0
for last day of the month) it's showing total as 0. I would like that to be
237531.8676 in my case below. Also Grand total is 0. How can I show numbers
there.

Thanks for your help.
Incremental New calc. member Cumulative
Month2 Week 6 20,127.38 205112.1476 1,398,611.28
Week 7 14,362.71 219474.8566 1,509,875.28
Week 8 18,057.01 237531.8676 1,626,561.89
Week 9 10,888.05 0 971,656.57
Total 63,435.15 0 5,506,705.02

Grand Total 248,419.92 0 10,502,504.87



"amitj" wrote:

Assuming that there are no fact table rows corresponding to empty days,
NonEmptyCrossJoin() can filter them out:

[Time].CurrentMember, [Time]. [Day Name]))).Item(0),
[Measures].[Cumulative])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hello,

I think instead of depending on the NonEmptyCrossJoin, you need to put
a flag in your time dimension indicating the "data load" status and
create this as member property on time dimension all levels. In the
formula, use the "data load" property to correctly find the last child
having data for open months else get the last child.

Hope this helps.

Ajit Singh
Host Analytics Inc.
www.hostanalytics.com

What if the Time dimension is shared among multiple cubes, but not all
of them share the same last data date?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Deepak,

In many business applications, the data is loaded for some members well
into future, or for some members, the data may end well before the
current valid time member. Hence we can not just depend on the
availability of data for aggregation. The last valid time members is
very important for reports since the last valid time member denotes the
time slice where all the measures and their aggregations are valid and
we need to ignore the data existing after the valid time member.

I handle this with appropriate property members along time dimension.
If the time dimesion is shared across multiple cubes, the appropriate
time member property is used in those cubes where it is needed to
restrict the data.

If there is a better way to handle than time member property in such
cases, it would be helpful to know.

Regards,

Ajit Singh
www.hostanalytics.com

Hi Ajit,

Your approach can certainly work where the problem is that some of the
cube data should be ignored, based on being after a global "valid date".
However, the problem described in this thread seems to be the more
common one of a closing balance - ie. the last (non-null) value of a
measure along a time dimension. There was no suggestion (at least, in
the problem discussion) that such a value might not be valid, and hence
need to be further filtered by date.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!