How to get Max / Recent date for each account

[Former Member]

Hi, I have Account Effective Date dimension created and I have also Account Dimension created. Each account has multiple Account Effective Date but I need to get only recent effective date (one) for each account. In attached example I have account 1005 and the max date or recent date is 2010-07-01 and there are various accounts. How to get the max one? How can I achieve this in Information Design Tool? Thanks

[former_member4998 · Accepted solution]

Hi

Create derived table to give rank on different account eff date with respect to each account PS.

Using below sample code and get the rank.

SELECT Account PS Account Effective Date

RANK() OVER (PARTITION BY...)

From Source tables

Where specify any conditons

Account PS	Account eff date	Account eff date Rank	Amount
1005	2003-01-01	3	12121
1005	2010-07-01	1	2323
1005	2005-11-01	2	452435
2008	2014-07-02	1	454
2008	2014-06-20	2	676
2009	2014-01-01	1	454

And then create object : account eff date Rank in business layer and then filter account eff date Rank =1 in the query filters in webi report.

Then in the report able to see recent Account eff date (one) for each account PS

[Former Member · Accepted solution]

Hi Dasari, Thanks for your help. My universe is multisource universe on the top of Oracle DB and Rank Over function seems to be not supported in MultiSource Environment. If we use single source universe then it is available. Thanks Bashir Awan

[Former Member · Accepted solution]

This is what I wrote to create a derived table. select @catalog('XXXXX')."SYSADM"."PS_LEDGER"."ACCOUNT", @catalog('XXXXX')."SYSADM"."PS_GL_ACCOUNT_TBL"."EFFDT", @catalog('XXXXX')."SYSADM"."PS_LEDGER"."POSTED_BASE_AMT", RANK () OVER (PARTITION BY @catalog('XXXXX')."SYSADM"."PS_LEDGER"."ACCOUNT" ORDER BY @catalog('XXXXX')."SYSADM"."PS_GL_ACCOUNT_TBL"."EFFDT" DESC) from @catalog('XXXXX')."SYSADM"."PS_LEDGER", @catalog('XXXXX')."SYSADM"."PS_GL_ACCOUNT_TBL" Where @catalog('XXXXX')."SYSADM"."PS_LEDGER"."ACCOUNT" = @catalog('XXXXX')."SYSADM"."PS_GL_ACCOUNT_TBL"."ACCOUNT"

[Former Member · Accepted solution]

Yes this is the correct answer and but the most recent one is not showing number 1. If we have three eff date it shows number 1 for oldest one and 3 for more recent one. In some cases we have two eff date for each account and in some cases 1 or three or more for each account. So if Number 1 shows the most recent one that is good. Any thought Thanks for your help. Bashir Awan

[former_member4998 · Accepted solution]

Hi

Try to include ORDER BY also....

SELECT Account PS Account Effective Date

RANK () OVER (PARTITION BY Account PS, Account Effective Date…etc ORDER BY Account PS, Account Effective Date…etc ) As RANK

From Source tables

Where specify any conditions

[Former Member · Accepted solution]

Hi, 

I am using Information Design Tool 4.1. I am trying something similar however was thinking of using "max" function in business layer.

In Business Layer, the two folders I am trying to use the max function is Unit Name and Last Outage Start Date

I am trying to get max last outage start date for each unit name. I tried the function as below but do not know how to bring in the unit name.

max(@Select(SALES_FORECAST\Data part\Outage\Last Outage Start Date))

Your input will really help me.

Thank you.