[DMR] Date aggregation

[Rosanero4Ever]

Hi all,

In my FM model I have a payments fact table with a date measure about the last payment date.
So I aggregate this date choosing  "maximum" aggregation rule.
But, when I perform an analysis using Analysis Studio or CWA  I see date formatted as the following:

20.130.930.000.000

This date should be 30/09/2013
I'm trying various formatting setting using FM but I have the same issue.
Can you help me, please?

thanks a lot for your advices.

[blom0344]

The date format itself can not be used for a measure. It is basically not possible to create an analysis with dates in the body. Looks like Cognos tries to circumvent this issue by formatting the date to a certain numeric type..

[Rosanero4Ever]

Does anybody can give me any hint, please?
I'm looking for a solution using a numerical type, as blom0344 wrote, without success.  :-\
Thanks in advance for your help

[adik]

don't apply the maximum aggregation rule to the date
instead create a query item where you compute the maximum date something like maximum([date])

[Rosanero4Ever]

adik, thank you for your answer.
I applied your advice but, even if the results are that I expected verifying query subjects in FM, in Workspace advanced (or in Analysis Styudio) the result is what I wrote in the initial posto, i.e. for example:
20.130.930.000.000
instead of 30/09/2013
Workspace in fact see the date measure as a long int  :-\
Does exist any other solution?
Many thanks for your time

[blom0344]

I do not really understand what you are attempting to do. A date should be used as a basis for a regular dimension. Why are you trying to use it as a fact object? You could perhaps transform it into  20131002  ( by using extract(year,[date])*10000+extract(month,[date])+extract(day,[date])  ) , but it would be treated as a normal measure if used in a crosstab (including rolling up)

[Rosanero4Ever]

I explain better my target:
I have a dimension customer and a fact table about the payments of each customer
Each payment is identified by an amount ad a date.
I would like extract, using my DMR view, the last payment date for each customer.
I'm thinking to evaluate the maximum payment date to perform this.

what do you think about?

Thanks!

[blom0344]

That would be an attribute, not a measure. You can define any number of attributes for a given dimension level but the relationship should be strictly 1:1 (or 0:1, the value is missing). Normally the last payment date would be added to the customer dimension when rebuilding the table.


In an analysis it would be a matter of right clicking the members (within the grid) and selecting the attribute through --> show attributes

[Rosanero4Ever]

you're right, but in this case If I analyze payments for a customer in the year 2012  probably the last payment could be 30/09/2013 but, because my analysis is focused on 2012, the last payment date should be 31/12/2012!  :-[
So, the last payment date should be dynamic based on the context of analysis

what do you think?
Thanks again for your time

[blom0344]

Still think a date is not suitable to be used as a measure.. A clever report may achieve what you want, but I doubt it will be easy within DMR / Analysis Studio