max date for dept id obiee 11.1.1.7.141014

962554

Subject Area is FMS AP - Purchasing Control

Case when "Fiscal Year and Month"."fiscal transaction dt"= MAX("Fiscal Year and Month"."fiscal transaction dt"  by "Dept(CF1)"."Dept Id") then "Fiscal Year and Month"."fiscal transaction dt" end

Using the above formula in column of an analysis.  Trying to get the maximum transaction dt by dept id so that it doesn't double/triple up amounts. Right now formula is running but returns nothing so no errors but not doing what i want it to do.

How can i make this formula grab the latest fiscal transaction dt by dept id?

Thanks

SriniVEERAVALLI

I've changed the expression as below

Case when "Time"."Date"= MAX("Time"."Date"  by "Time"."Month") then "Time"."Date" end

I do see some data wherever its applicable as per logic.

I think you need to refine the expression with else part, I dont see any value in my local beside some metrics ;(

When I set is not null to the expression(D1) field I see the below

962554

Interesting Srini.  I was trying to grab last record in the fiscal year as the way it is put together.  The amounts in sum of the fields are cumulative and double up unless I grab the last transaction date.  I thought maybe in column I could flag the one with maximum date and just filter on flag but I stumbled on filtering transaction date as ranked last 1 and it works great.  I found a solution but curious have you seen where anyone tries to flag last transaction and filter on that flag based on date?  Is there a way to do it like the ranked last 1 filter?   That is what I was initially thinking of doing.

962554

Sorry jumped the gun.  Still struggling with this one.  Trying to get the row of data/amounts for the last transaction date without it accumulating amounts.

962554

it worked with this set as a column TOPN("Fiscal Year and Month"."fiscal transaction dt",1 by "Dept(CF1)"."Dept Id").  Seems to be solid.