Hello all, greeting from Peru.

I have a problem with semiadditive measures in AS 2000.
Yes, I read the microsoft webpage MSDN of Semiadditives, and I write my
code based on it.

I have stock form a product of the month JANUARY, since January 3 from
January 29.

Here is my first mdx work:

Measures.[No void]:
===============================
IIf(
(IsEmpty(([Measures].[Stock END],[Time].currentmember))),
([Measures].[No void],[Time].currentmember.prevmember),
([Measures].[Stock END],[Time].currentmember)
)
Measures.[Closing Value]:
========================
(Measures.[No void], ClosingPeriod([DimFecha].[Day]))


The Result:
==========
As I have cells of products that not have stock by some days, in the grid
the calculated member (No void) goes to the INFINITE, and the meassure
closing value have ERROR in its cells.

I modified the (No void) MDX sintaxis to have this:

Measures.[No void] Number 2:
===============================
IIf(
(IsEmpty(([Measures].[Stock End],[Time].currentmember))),
(IIf(
(([Time].currentmember) is (OpeningPeriod([Time].[Day])) ),
( [Measures].[Stock End],[Time].currentmember ),
([Measures].[No void],[Time].currentmember.prevmember))
),
([Measures].[Stock End],[Time].currentmember)
)

The result:
Still doesnt make te result form END JANUARY the value of JANUARY 29 (the
last value in the row, remember I have values from january since 3 to 29)

I want to try to make a comnparisson between TIME.CURRENTMEMBER and
openingPeriod of the selection (Comparing the 31 with January the 1st, then
30 vs 1st, 29 vs 1st, and so) but I dont ave this clearly.

Finally, this is the last of my MDX for NOVOID

MDX LAST
========
CoalesceEmpty(
([Measures].[Stock End], [Time].CurrentMember ) ,
(
(IIf(
(([Time].currentmember) is (OpeningPeriod([Time].[Day])) ),
( null ),
([Measures].[No void],[Time].currentmember.prevmember))
)
)
)

Please, I need some advice with this. MSDN only helped me with the first
part of my letter. And his recursively algoritm brings me to the INFINITE.
I Realy need help,

Thanks a lot, for the time.

--
Message posted via http://www.sqlmonster.com

I have used these formulas:
http://sqlserveranalysisservices.com...20AS2005v2.htm
(adapted to AS2000)
and all works fine

try it ;-)

"Andrew Alfaro via SQLMonster.com" <forum (AT) nospam (DOT) SQLMonster.com> wrote in
message news:632dad0a0af84f6ea6a7bfbc41412788 (AT) SQLMonster (DOT) com...

Hi Andrew,

Hopefully you can get the formulas from the paper mentioned by Jéjé to
work, since they are more efficient. But I tried to modify your final
formula for the Foodmart Warehouse cube as follows, and it seems to
work. The main change is that the original Time member has to saved and
used in OpeningPeriod(), otherwise it will always be true:

'CoalesceEmpty([Measures].[Warehouse Sales] ,
IIf([Time].currentmember is
OpeningPeriod([Time].[Month], StrToSet("TOrg").Item(0)),
Null, ([Measures].[No void], [Time].prevmember)))'

Member [Measures].[Closing Sales] as
'Sum({Time.CurrentMember} as TOrg,
(Measures.[No void], ClosingPeriod([Time].[Month])))'

Select {[Store].[All Stores].[Canada]} on columns,
Descendants([Time].[Year].Members,
[Time].[Month], SELF_AND_BEFORE) on rows
from Warehouse
where ([Measures].[Closing Sales])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak and Jeje for your interest in helping me with that problem.
I tried lot of MDX and all have good results.

The problem I told to you gets easier now:

1.- I dont have the Dimension DATE as Year/Month/Day
I rename it to MONTH and there I have All months since JAN04 to DEC05
This mean my last stock or STOCKEND value is April Stock, in APR05
May05 no value
I used Deepak recursively algorithm (and other algoritms) and for
STOCK_END cell with empty values have now CERO (0)

2.- I must obtain Stock_END for Total_of_Dimension, the value in APR05
I mean, TOTAL_MONTH:
STOCKINIT ---> JAN04 (I have the right value and solved this
with openperiod)
STOCKEND ----> APR05 (Here is the matter)

3.- There is the last MDX I used, adapted from a webpage, I dont understand
too much the lastperiods(30, where originally was lastperiods(1, and the
other value marked in the MDX. When I changed to 12 get 12 cells before,
meaning that value of SEP05 was in SEP04

NOVOID
======
iif( IsNull([Measures].[Stock]),
(Tail(filter ({lastperiods(30,[Month].currentmember)},
Not IsEmpty([Measures].[Stock])
),
1 //<------this value i dont understand too
).item(0).item(0),
[Measures].[Stock]
),
[Measures].[Stock]
)

STOCKEND
========
([Measures].[NOVOID],ClosingPeriod([Month], .CurrentMember))


4.- I desire to have this Total_Month as total of April05, if is possible
stock in MAY05, JUN05,JUL05, can have the value of APR05 too, so the
MONTHTotal dont need more code.


Thanks a lot, all of you are so good with this matter.

If I find a solution, I´ll post it too.

Sincerely,


....Andrew

--
Message posted via http://www.sqlmonster.com

Hi Andrew,


The "1" that you pointed to is an argument of Tail(), so that only the
last (non-empty) month is selected:

1 //<------this value i dont understand too


But I'm not clear what results you're getting, compared to what you want
- can you give some sample data?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Hello again Deepak,

the problem I had is no more, the boss changed tactic.
The matter is simple:

If I have the measure STOCK, and the calculated cells STOCKINIT and
STOCKEND, this is the result

JAN FEB MAR APR MAY JUN ....> DEC
STKINT - 100 40 200 30 40
STKEND - 40 200 30 40 50

In both cases using opening period and closing period, but when Totalizing
I have:

2005
STKINT - 100
STKEND - <---empty

So this value must be the stock of last period with value, or MAY (50 in
the example)


One tactic was to fill next months with

...MAY JUN JUL AGO SEP OCT NOV DEC
STKINT - ...40
STKEND - ...50 50 50 50 50 50 50 50

So when sumarizing the 50 will be STOCKEND value.

But my organization leave this work because when using the Cube in Excel
and choose a Range, this range don´t calculate the real values.

so If I choose FEBR and MARCH excel gaves me the openperiod (January value)
and close period (December value, or void)

I only want to know this not for my work, now I want to know some solution
for knowledge.

hope Analysis Services 2005 get this easier

Thanks a lot!

--
Message posted via http://www.sqlmonster.com

Hi Andrew,

Not much that I can add to the papers already mentioned in this thread
and their examples. But Analysis Services 2005 should make semi-additive
measures much easier:

http://www.microsoft.com/technet/pro...uate/dwsqlsy.m
spx
Calculations and Analytics

One of the greatest arguments for using an analytical server such as
Analysis Services is the ability to define complex calculations
centrally. Analysis Services has always delivered rich analytics, but
some complex concepts have been difficult to implement.

One such concept is that of a semi-additive measure. Most common
measures, such as [Sales], aggregate cleanly along all dimensions:
[Total Sales] for all time is the sales for all products, for all
customers, and for all time. A semi-additive measure, by contrast, may
be additive in some dimensions but not in others. The most common
scenario is a balance, such as the number of items in a warehouse. The
aggregate balance for yesterday plus today is not, of course, the sum of
yesterday's balance plus today's balance. Instead it's probably the
ending balance, although in some scenarios it is the beginning balance.
In Analysis Services 2000 you would have to define a complex MDX
calculation to deliver the correct measure. With Analysis Services 2005,
beginning and end balances are native aggregation types.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks a lot Deepak and SQL Forum, all of you really know this matter.

Thanks for all your time helping me with this common problem for all the
world.

Your friend,



....Andrew Alfaro

--
Message posted via http://www.sqlmonster.com