Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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)
Would that return the value for a distinct APP_DATE for whichever row has the max FCST_DATE? I am still seeing multiples.
What exactly are you hoping to get? Share a sample with expected output if possible?
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)
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)
Apparently I am only supposed to include FCST_AMT for dates in the future. That would help explain it.
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.
Closing this thread