How to get value for last work date value from IDT universe.

[Former Member]

Hello to All,

How can we get measure values for last work date of the Month -> Quarter -> Year from the IDT universe.

We have a requirement to drill the data on date dimention on Explorer and WebI reports and the Amount should be Amount as on last working day for Month , Quarter and Year.

For Eg:

We have a Amount Fact table which has columns Date and Amount and a Date dimention table

Thanks,

Sonia W

[Former Member]

Hi All,

Thanks for comments,

But these solutions does not fit in our requirement in the way we want:

Let me illustrate more:

1) Consider the scenario below where  we have pulled Date, Month, Year and Account Balance.

Date	Month	Year	Account Balance
6/1/2012	6	2012	21023.2
6/2/2012	6	2012	21023.2
6/3/2012	6	2012	21023.2
6/4/2012	6	2012	21023.2
6/5/2012	6	2012	21023.2
6/6/2012	6	2012	21023.2
6/7/2012	6	2012	21023.2
6/8/2012	6	2012	21023.2
6/9/2012	6	2012	21023.2
6/10/2012	6	2012	21023.2
6/11/2012	6	2012	21023.2
6/12/2012	6	2012	21023.2
6/13/2012	6	2012	21023.2
6/14/2012	6	2012	21023.2
6/15/2012	6	2012	21023.2
6/16/2012	6	2012	21023.2
6/17/2012	6	2012	21023.2
6/18/2012	6	2012	21023.2
6/19/2012	6	2012	21023.2
6/20/2012	6	2012	21023.2
6/21/2012	6	2012	21023.2
6/22/2012	6	2012	20704.2
6/23/2012	6	2012	19343.2
6/24/2012	6	2012	18875.2
6/25/2012	6	2012	18875.2
6/26/2012	6	2012	132729.92
6/27/2012	6	2012	132729.92
6/28/2012	6	2012	132729.92
6/29/2012	6	2012	132729.92
6/30/2012	6	2012	132729.92

2) If we pull out Date field from the report then you get output as below (As we have kept the PROJECTION in universe as SUM for Account Balance)

Month	Year	Account Balance
6	2012	1182934.6

But our requirement is to display Account balance as of last day of month as displayed below. Same would be the case for YEAR Account  Balance. We need to do it on  a single column instead of using additional columns. Please let us know if there’s a workaround.

Month	Year	Account Balance
6	2012	132729.92

P.S : We are not in favour of creating a seperate column / view / derived table.

Thanks,

Sonia W

[Former Member]

Hi,

Above soultions will give you want you need, if you are looking for balance as of a paticular date.

If ditrived table is not a solution , I am not sure wheteher  this works but you can give it  try , There is a day number of month variable exist in universe , try creating  object for maximum date in each month and using this modify your measure something like.

Sum( case when calender date = MaxMonthdate object then Account Balance end).

Thanks,
Raja

[Former Member]

Hi Sonia,

Try to incorporate the logic using CASE  or you need to created a derived tables or calculated columns in Date master .

Quick solution would be create a derived table with below logic , join this with fact , create objects and valiadte, this will work .

SELECT * FROM

DATE_MASTER

WHERE

CALENDAR_DATE

IN (SELECT   MAX(CALENDAR_DATE)

FROM     DATE_MASTER

GROUP BY MONTH(CALENDAR_DATE), YEAR(CALENDAR_DATE)

)

Thanks,
Raja

[Former Member]

Sonia,

The solution Raja Mohan Reddy Gade mentioned will work... However the base calender table which you would use for derived table should only have work day data ( Try using a MINUS of the table for Holiday and Weekends)..

If you use a regular calendar you will get the value for last day for the month and not for last work day..

Once you have that the approach will work like charm..

[Former Member]

Hi Sonia,

To get the highest or the last date from date field you cannot use Max in Dimensions or Measure objects in IDT as it is aggregate function.

You have to create a Derived table in Data foundation which gives you the last date from Date field, then you can use derived table field in Business layer to create objects to get the Amount for the last date.

Here Maxyear gives you the last year, which is from Derived table.

Above is derived table sql snapshot, which you have to create in Data foundation of the universe

Hope above information helps you,

Regards,

Ambreen