Get Maximum And Minimum Year From Resident Table

[rebelfox]

I have a QVD file of loaded date with a date and year field.

I want to limit my generated calendar for the date to the range of years between the minimum and maximum value in the year field.

I have coded:-

MinMaxDate:

Load min(InvoiceYear) As  vMinYear

         max(InvoiceYear) As vMaxYear

resident MyLoadedQVD;

TRACE >>1>> $(vMinDate);

TRACE >>2>> $(vMaxYear);

The trace shows no value for vMinDate or vMaxDate.

I don't understand why.

The InvoiceYear field has values and no nulls.

Do I have to declare the variables first in some way for them to receive the min() and max() values?

Any advice appreciated.

[sunny_talwar]

Try this:

MinMaxDate:

Load min(InvoiceYear) As  MinYear

         max(InvoiceYear) As MaxYear

resident MyLoadedQVD;

LET vMinYear = Peek('MinYear');

LET vMaxYear = Peek('MaxYear');

TRACE >>1>> $(vMinDate);

TRACE >>2>> $(vMaxYear);

[Digvijay_Singh]

Small correction -

TRACE >>1>> $(vMinYear);

[sunny_talwar]

good eye

[rebelfox]

Yes that sorted it.  Thanks.

[swuehl]

Roy, it seems there was initially a little confusion about how to use variables vs. table fields.

Has this been sorted as well?

If you want to get min / max values to create a calendar, you can even get rid of the variables completely (following taken from Rob's Better Calendar Scripts | Qlikview Cookbook , always a good read, and modified slightly for year extrema), have a look at lines 19ff:

MasterCalendar:
Load
TempDate AS InvoiceDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;

//=== Generate a temp table of dates ===
LOAD
date(Makedate(minyear) + IterNo()-1) AS TempDate
WHILE Makedate(minyear) + IterNo()-1 < Makedate(maxyear+1);

//=== Get min/max year from Field ===/
LOAD
min(FieldValue('InvoiceYear', recno())) as minyear,
max(FieldValue('InvoiceYear', recno())) as maxyear
AUTOGENERATE FieldValueCount('InvoiceYear');

[Colin-Albert]

Do you really need to generate the max and minimum date?

Often the start date is known and can be hard-coded, and the end date can be calculated based on todays date.
It may be today(), yearend(today()) or possibly today + 12 or 24 months.

Using a simple expression like this can be significantly faster than calculating the min and max date every time you build the calendar, especially when dealing with large data sets.