Set Analysis Syntax using Max(Date)

[cbaqir]

I need to modify this syntax to only include the max(FCST_DATE) and apparently this is not the right way to do it because I am still ending up with multiple FCST_DATEs for each APP_DATE:

=Sum({$<FCST_DATE={"<$(=max(FCST_DATE))"},EXP_TYPE_CODE={'Capital'},APP={'FORECAST'}>}FCST_AMT)

What am I doing wrong?

[swuehl · Accepted solution]

If you only want the max date, you shouldn't use the < comparison, and I think you should format the max(date) using Date() function:

=Sum({$<FCST_DATE={"=$(=Date(max(FCST_DATE),'YYYY-MM-DD'))"}, EXP_TYPE_CODE={'Capital'},APP={'FORECAST'}>}FCST_AMT)

[cbaqir]

Would that return the value for a distinct APP_DATE for whichever row has the max FCST_DATE? I am still seeing multiples.

[sunny_talwar]

What exactly are you hoping to get? Share a sample with expected output if possible?

[cbaqir]

Here's what I can share... this SQL query returns the correct result for the Fiscal Year.

select b.fiscal_year,  SUM(fcst_amt) FCST_AMT

from it_dw..app_eld   a

   join it_dw..DM_STAT_CAL_0 b on (a.TRX_DATE = b.TRX_DATE)

where APP  = 'FORECAST'

and EXP_TYPE_CODE = 'Capital'

and b.fiscal_year = 'FY16'

  and a.FCST_DATE = (select MAX(fcst_date)

   from   it_Dw..APP_ELD

   where  APP = 'FORECAST'

   )

group by b.FISCAL_YEAR

order by 1 desc, 2 desc

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

It's the subquery part I am trying to replicate in QV:

and a.FCST_DATE = (select MAX(fcst_date)

   from   it_Dw..APP_ELD

   where  APP = 'FORECAST'

   )

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

QV:

=Sum({$<FCST_DATE={"=$(=Date(max(FCST_DATE),'YYYY-MM-DD'))"}, EXP_TYPE_CODE={'Capital'},APP={'FORECAST'}>}FCST_AMT)

[swuehl]

Then you should probably add the APP filter to the dollar sign expansion aggregation:

=Sum({$<FCST_DATE={"=$(=Date(max({<APP = {'FORECAST'}>} FCST_DATE),'YYYY-MM-DD'))"}, EXP_TYPE_CODE={'Capital'},APP={'FORECAST'}>}FCST_AMT)

[cbaqir]

Apparently I am only supposed to include FCST_AMT for dates in the future. That would help explain it.

[cbaqir]

So, it turns out that I need to add the following piece to this:

=Sum({$<FCST_DATE={"=$(=Date(max({<APP = {'FORECAST'}>} FCST_DATE),'YYYY-MM-DD'))"}, EXP_TYPE_CODE={'Capital'},APP={'FORECAST'}>}FCST_AMT)

Only include FCST_AMTs for dates equal to or greater than the max(APP_DATE) where ACT_AMT >0.

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.

[cbaqir]

Closing this thread