Hello All,

I am trying to get a measure that will sum the sales for this year.

For example
January Sales =10000
February Sales=20000

Thus in AS I want to see the Cumulative Sales for February=30000

What is the best way to do this?

Thanks

Clint

Check for the ytd() function. It would be something like

sum(ytd([Time].[Calendar]), [Measures].[Sales])

Offcourse, to get results you would have to include time dimension in the
query.


MC

"Clint" <ccpatriot12 (AT) yahoo (DOT) com> wrote

Hmm..

YTD isnt quite what I am after...

Here is an example of what I have currently..

Using the Foodmart 2000 cube (SALES)

WITH MEMBER Measures.[Cumulative Sales] AS 'SUM( HEAD( ORDER(
{[Time].Siblings}, [Measures].[Unit Sales], BDESC ) AS OrderedSiblings,
RANK( [Time], OrderedSiblings ) ), [Measures].[Unit Sales] )'

SELECT

ORDER( DESCENDANTS( [Time].&[1997] ), [Measures].[Unit Sales], BDESC ) ON
COLUMNS,

{[Measures].[Unit Sales], [Measures].[Cumulative Sales]} ON ROWS

FROM [Sales]



Basically what I want to see on Columns... are the months of 1997... and
each month is cumulative..

The above code kind of works, but doesnt focus purely on month. I need some
help on that.



Thanks!!

"MC" <marko_culo#@#yahoo#.#com#> wrote

Could you explain why something like doesnt work:

WITH MEMBER
[measures].[cumulative sales]
as
'
sum(ytd([Time].[Calendar]), [Measures].[Sales])
'

select
[Time].[Calendar].[Month].members, --- put here descendants of 1997 or
something like that
[measures].[cumulative sales] on rows

from.....


MC

"Clint" <ccpatriot12 (AT) yahoo (DOT) com> wrote

You know what... YTD will indeed work... Now why doesn't any source MDX book
or otherwise detail this as a solution... Why do they go into order, head
and rank... sigh..

Thanks!!! I really appreciate all your help MC.
"MC" <marko_culo#@#yahoo#.#com#> wrote

Dont know, never read them . Feel free to contact me if you need more
help.

MC

"Clint" <ccpatriot12 (AT) yahoo (DOT) com> wrote

Thanks for everything...
"MC" <marko_culo#@#yahoo#.#com#> wrote