Hi,
I have the granularity in fact table as Activity,Customer,Time
Certain defined activities are created which are done at the customer
Store like checking the retaiol Audit which have measures like count
of Product,Displayed price of the product etc.

I wanted to get the Last visit made to the selected customer as
calculated member in Activity dimension.

This Last visit is the Max date in the Fact table for this customer.

If I select this calculated member stored in the Activity dimension it
should show me all the measures for the selected customer for the Last
visit date done to this customer.

Any help is highly appreciated

regds

for the selected customer i need Max timeid from the cube using MDX
i was trying this query

iif([SALES FORCE].CurrentMember.Level.Ordinal=[SALES FORCE].[VISIT TO
CUSTOMER].Ordinal,Max(descendants([Time].[Weekly].currentmember,
[Time].[Weekly].[Date]) , [Time Version].&[1]),Null)


but this gives me the timeid which has max records rather than the Max
timeid

but i want something like this

timeid Customer_id Activity_id unit_sales
====== =========== ========== ===========
1511 1 2 100
1511 1 3 200
1512 1 2 50
1513 1 2 20


I want 1513 to be displayed but the above query gives me 1511

Please help


tawargerip (AT) hotmail (DOT) com (Prasanna) wrote in message news:<d4b0bad7.0408260410.355a76e2 (AT) posting (DOT) google.com>...

How is timeid exposed in the cube - is it a key or member property of a
Time dimension? Max() returns the highest value of the second argument
(numeric expression) over the first argument(set). So, in this case, how
does the expression: [Time Version].&[1] return a timeid value?


- Deepak

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

Hi,
[Time Version].&[1] is a dummy record which has value 1 in all the
records in the fact table.
Period is the time dimension and Period_id is the key exposed in cube
as timekey

Appreciate your help

Regards

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

Still not sure of all the details, but it looks like your Max() numeric
expression is defaulting to [Unit Sales] (assuming that is the cube's
default measure).

Here's a query for Foodmart Sales that should be similar to what you're
trying - the [MaxTime] measure returns the maximum month key over all
months in which there were sales to the selected customer. In the case
of [Alice Cantrell], sales occurred in Aug, Oct & Dec, so max is 12:

'StrToValue([Time].CurrentMember.Properties("Key"))'
Member [Measures].[MaxTime] as
'iif([Customers].CurrentMember.Level is [Customers].[Name],
Max(NonEmptyCrossJoin(Descendants([Time].CurrentMember,
[Time].[Month]), {[Customers].CurrentMember}, 1),
[Measures].[TimeKey]),Null)'
Select {[Measures].[MaxTime]} on columns,
{[Time].[1997]} on rows
from Sales
where ([Customers].[All Customers].[USA].[CA].[Altadena].[Alice
Cantrell])

- Deepak

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

Try combining the TAIL function with non empty crossjoin.

Somrething like (untested)

(TAIL(NonEmptyCrossJoin(descendants([Time].[Weekly].currentmember,
[Time].[Weekly].[Date]) ,{[Time Version]},1),1),[Time Version])

"Prasanna" <tawargerip (AT) hotmail (DOT) com> wrote