max date for dept id obiee 11.1.1.7.141014

Question

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

962554 · Answer

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.

962554 · Answer

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 · Answer

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.