Hi group,

I am a newbie to MDX but an SQL user for a long time. I am working for
an insurance company.

Here we go: My fact table contains a measure 'Contract Value'. This
value changes every now and then when the customer changes conditions
on his contract. The changes are reflected in a dimension
'date_loaded', for each time the customer changes his contract state
because a new row is inserted in the fact table. Customers are
represented by a 'Contract Number' which is unique for each customer.

There are three dimensions in my cube 'Contract Value', 'date_loaded',
and 'Contract Number'. What I'd need is the 'Contract Value' for each
Contract Number' with the biggest, i.e. latest 'date_loaded', put as
MDX. Only 'Contract Value' is a measure.

Tried to figure it out myself without much success.
Please help.

Regards
Bernard

Moin Bernhard,

/* In german
Hast du noch AS2K oder schon AS2K5? Wenn schon AS2K5 EE/DE, dann ändere den
Aggreationstyp des Wertes auf semiadditive "last non empty".
Wenn du noch mit AS2K zu tun hast, müsstest du "zu fuß" einen CM basteln, um
die letzte nicht leere Celle die Zeitachse entlang zu emitteln.

Mfg
Vladimir Chtepa.
*/

Have you AS2K or AS2K5? In the AS2K5 EE/DE you schould use semiadditive
measure.
In the AS2K You should write a calculated measure to calculate last non
empty cell on the time axis.

Thanks,
Vladimir Chtepa


<bspa (AT) gmx (DOT) de> schrieb im Newsbeitrag
news:1133797617.507230.305490 (AT) g44g2000cwa (DOT) googlegroups.com...

Thanks Vladimir,

I am still using AS2K. Would you mind elaborating on semiadditive
measure. I just don't know what this means in the context of AS2K.

Cheers
Bernard

I have gotten this far:
WITH MEMBER Measures.[Last Non Empty Value]
CoalesceEmpty((Measures.[Contract Value],[Contract
Number].CurrentMember,[Date Loaded].CurrentMember), (Measures.[Last
Non Empty Value],[Date Loaded].CurrentMember.PrevMember))

What i still need is the date_loaded values for those last non empty
contract values . Date_loaded values are currently members of the
date_loaded dimension.

With the selection here:

Select {(Measures.[Last Non Empty Value])} ON Columns,
NonEmptyCrossjoin({[Contract Number].[Contract Number].members},{([Date
Loaded].Members)}) ON ROWS
FROM
contractValues

I only get this

[Contract Number] [Date Loaded] [Last Non Empty Value]
101513178 All Date Loaded 98240,48
101513178 2005-07-15 00:00:00 49120,24
101513178 2005-09-22 00:00:00 49120,24
101513180 All Date Loaded 51675,57
101513180 2005-07-15 00:00:00 17225,19
101513180 2005-08-17 00:00:00 17225,19
101513180 2005-08-26 00:00:00 17225,19

Note that I only want the last date to show in the date_loaded column.


Its probably very simple, for an experienced MDX programmer. Please
drop a hint or two :-)

Cheers
Bernhard