Inline any date (i.e. '01-03-2011') and subtract 90 days

[#1]

Hello all.  I'm new to this still.  I need to create a report inputing different dates (i.e. 01-03-2011) and be able to subtract 90 days from that date.  I'm having problems determining what format QV will understand in order to do this.  I have an inline input table for my date in that format, MM-DD-YYYY.  Or, even if I hardcode the date in the code, how can I format the date in order to use it as the end date, then determine my start date, 90 days prior?  Thanks.

[johnw]

SET DateFormat='MM-DD-YYYY';

LOAD *
,date(EndDate-90) as StartDate
INLINE [
EndDate
01-03-2011
];

[#3]

That's great John.  I like that format.  How about if I want to create two variables.  One with the StartDate of 90 days prior and one with the hardcoded date?  I guess, I don't know how to reference that table date when I do my SQL selection criteria.  A variable would work better.  Thanks.

[johnw · Accepted solution]

If you're hardcoding just two dates, and only want to use them in SQL, why create a table or variable at all?  That's just straight up SQL.  But if you need variables for some reason:

LET vEndDate = makedate(2011,1,3);

LET vStartDate = date(vEndDate-90);

But those are QlikView dates, not SQL dates, or however you store your dates.  In our system for example, most dates are stored as an integer in format YYYYMMDD.  So I'd want to format my variables that way:

LET vEndDate = date(makedate(2011,1,3),'YYYYMMDD');

LET vStartDate = date(vEndDate-90,'YYYYMMDD');

Something:

LOAD ...

;

SQL SELECT ...

FROM ...

WHERE MyDate BETWEEN $(vStartDate) AND $(vEndDate)

;

But that seems like a lot of complication for no reason when I could just write this instead:

Something:

LOAD ...

;

SQL SELECT ...

FROM ...

WHERE MyDate BETWEEN 20101005 AND 20110103

;

[#5]

Let me clarify.  Below is my code.  If I can convert the hardcoded date into a variable, then I can subtract the 90 days.  I guess what I need is how to convert a hardcoded date into a variable date.  Thanks so much!

ZEDY4QV:
Load
Year(BUDAT) & '-' & Month(BUDAT) & '-' & trim(

[Bakery Desc]) as %BakeryKey,
*
where BUDAT >= '$(vWeekEnd90Date)'    // Previous 90 Days from any given date
AND BUDAT < '2011-01-03'
;

Load

*

FROM

[$(vQVDPath)ZEDY4QV.qvd] (qvd);

[#6]

Thank you so much, John.  You should be teaching this!!!