Select Top 1 Qlikview Formual

[michaelmalloy]

  I am trying to write a formula in Qlikview that selects the top row

Like: 

SELECT        TOP (1)  current_balance

  FROM            gl_balance_fact

  WHERE        balance_date >= '03/31/2017'

Below is the formula I currently have (it currently sums all g/l dollars less than equal to 03/31/2017.  I need the last balance that is before or equal to 3/31/2017

LET edbl_PL_YTD = 'sum({<PandLOperExpType={''Top''},   [Event Month/Year]=, _MonthNbr={"' & chr(60) & chr(61) & chr(36) & '(vintSelMonthNbr)"}>} [G/L Dollars])';

[arvind1494]

make it order in a descending and then limit it as a 1

[RadovanOresky]

Hi,

Try to use FirstSortedValue function with negative sorting on Date. Something like:

FirstSortedValue(current_balance, -balance_date)

[michaelmalloy]

Thanks!