Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
What I want is to display one row for each APP_DATE with the max(FCST_DATE) showing as 12/31/2015.
I have created a Variable for max(FCST_DATE) = vMaxFCST_DATE
Only include FCST_AMTs for dates equal to or greater than the m
ax(APP_DATE) where ACT_AMT >0 (APP='ACTUAL').
For example, even though we are in February, we won't yet have any ACT_AMT data for APP_DATE in January so the FCST_AMT sum SHOULD include the FCST_AMT from APP_DATE in January.
I need some help to get the view above.
Sum({$<APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"},FCST_DATE={$(vMaxFCST_DATE)}>}FCST_AMT)
Compare it with this one:
Sum({$< APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, FCST_DATE={"$(=date(max(FCST_DATE), 'MM/DD/YY')))"}>}FCST_AMT)
Maybe this:
=round(Sum({$<FISCAL_YEAR=,APP_DATE={">$(=date(Max({<ACT_AMT={'>0'}>}APP_DATE), 'MM-DD-YYYY'))"}>}FCST_AMT), 1000)/1000
Thanks - that helps for the <APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"} part.
What's wrong with this syntax?
=Sum({$<APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, FCST_DATE=date(max(FCST_DATE), 'MM/DD/YY'))>})FCST_AMT)
Compare it with this one:
Sum({$< APP_DATE={">$(=Max({<ACT_AMT={'>0'}>}APP_DATE))"}, FCST_DATE={"$(=date(max(FCST_DATE), 'MM/DD/YY')))"}>}FCST_AMT)
that gives me Error in set Modifier Expression.
Works for me: