topic Re: Select Top 1 Qlikview Formual in QlikView

Select Top 1 Qlikview Formual

michaelmalloy — Fri, 26 Jan 2018 22:05:48 GMT

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])';

Re: Select Top 1 Qlikview Formual

arvind1494 — Mon, 29 Jan 2018 08:08:55 GMT

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

Re: Select Top 1 Qlikview Formual

RadovanOresky — Mon, 29 Jan 2018 08:25:51 GMT

Hi,

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

FirstSortedValue(current_balance, -balance_date)

Re: Select Top 1 Qlikview Formual

michaelmalloy — Fri, 02 Feb 2018 05:15:24 GMT

Thanks!