Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Set Analysis Syntax using Max(Date)

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?

2-1-2016 3-58-32 PM.jpg

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

8 Replies
swuehl
MVP
MVP

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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

2-2-2016 8-29-50 AM.jpg

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
MVP
MVP

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
Specialist II
Specialist II
Author

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

cbaqir
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

Closing this thread