How do you set a Min and Max date based off different fields within the same table?

[#1]

Hi,

Could anyone offer some advice on the following.....

I am currently using the following symtax to try and set the min and max dates within my document:

 LET varMinDate = Num (Peek('EstProjStart', 0,'Projects')); 

LET var

 varvarMaxDate = Num (Peek('EstProjFin', -1,'Projects'));

I appreciate that I need to order the Projects table, however, because I need to take the minimum value from one field and and the maximum value from another field I am not quite sure how to best tackle this. I am also bringing this data in from excel not access like in a lot of the course material. The Projects table symtax can be seen below:

Projects:

 LOAD









RegionID,

 //EstProjStart,









PhaseProjManager As ProjectManager,
FROM
[Data files\Natural Resources data.xlsx]
(
 ooxml, embedded labels, table is Projects); //ActProjFin, //EstProjFin, //ActProjStart, ProjectName,
ProjectDescription
 ,

Any guidance would be most appreciated.

Thanks,

Carl

[disqr_rm]

What's the varMaxDate value? Just put it in a text box and post here.

Also where are you defining varMaxDate? LET varMaxDate = ?????.

[#3]

The production dates fall within the varMaxDate and the varMinDate.

I have no decided to place the production table after the calendar table and it now works - but would prefer to have the production table in the facts tab which is before the calendar.

This is my code (I have defined the Let statements before the calendar table on the calendar tab:

Let varMinDate = peek('MinEstProjStart');

Let varMaxDate = peek('MaxEstProjFin');

Let varToday = today();

Let NoOfDays = varMaxDate - varMinDate + 1;

drop table MinMaxTab;

MasterCalendar:

LOAD

*,

week(Date) AS Week,

year(Date) AS Year,

month(Date) AS Month,

day(Date) AS Day,

weekday(Date) AS WeekDay,

'Q' & Ceil(Month(Date)/3) AS Quarter,

date(monthstart(Date), 'MMM-YYYY') AS MonthYear,

week(Date)&'-'&Year(Date) AS WeekYear,

Year2Date(Date, 0, 1, $(vToday))*-1 AS CurYTDFlag,

Year2Date(Date,-1, 1, $(vToday))*-1 AS LastYTDFlag;

LOAD

date($(varMaxDate) - recno() + 1) as Date

AUTOGENERATE

NoOfDays;

//********** Production Table **********

Production:

LOAD

ProjectName,

Date,

ProductionAmount

FROM

[Data files\Natural Resources data.xlsx]

(ooxml, embedded labels, table is Production);

[disqr_rm]

oh! ofcourse, if you had a Date field before load, that would't work. What you could do is use some other field name than Date in the Calendar, and later rename it or load it again in pre-load as Date.

Example:

LOAD *,
week(dummyDate) AS Week,
...
...
dummyDate as Date;
LOAD
date($(varMaxDate) - recno() + 1) as dummyDate
AUTOGENERATE
NoOfDays;

drop field dummyDate;

Anyway, glad that it is working for you now.