Qlik Community

Ask a Question

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Specialist
Specialist

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

Specialist
Specialist

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?

Specialist
Specialist

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)

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)

Specialist
Specialist

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

Specialist
Specialist

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.

Specialist
Specialist

Closing this thread