topic Re: Set Analysis Syntax using Max(Date) in QlikView

Set Analysis Syntax using Max(Date)

cbaqir — Mon, 01 Feb 2016 23:01:13 GMT

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?

Re: Set Analysis Syntax using Max(Date)

swuehl — Mon, 01 Feb 2016 23:16:38 GMT

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)

Re: Set Analysis Syntax using Max(Date)

cbaqir — Tue, 02 Feb 2016 15:00:49 GMT

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

Re: Set Analysis Syntax using Max(Date)

sunny_talwar — Tue, 02 Feb 2016 15:20:21 GMT

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

Re: Set Analysis Syntax using Max(Date)

cbaqir — Tue, 02 Feb 2016 15:31:23 GMT

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)

Re: Set Analysis Syntax using Max(Date)

swuehl — Tue, 02 Feb 2016 15:34:22 GMT

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)

Re: Set Analysis Syntax using Max(Date)

cbaqir — Tue, 02 Feb 2016 15:43:06 GMT

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

Re: Set Analysis Syntax using Max(Date)

cbaqir — Tue, 02 Feb 2016 15:54:52 GMT

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.

Re: Set Analysis Syntax using Max(Date)

cbaqir — Tue, 02 Feb 2016 16:32:25 GMT

Closing this thread