topic Generating Months, Quarters & Years between two dates in QlikView

Generating Months, Quarters & Years between two dates

Fri, 31 Dec 2010 10:25:24 GMT

Dear All,

I have a query regarding creation of period basis the start date and end dates.

ex: I have a contract starts from 20-Dec-2010 and ends 30-jun-2011, then i want to see the information as number of days for each month, qtr & year

Please help me in this asap.

Generating Months, Quarters & Years between two dates

pover — Sun, 02 Jan 2011 18:57:08 GMT

Just to make sure I understand correctly, do you want to generate the years (2010, 2011), months (December, January, February, March, April, May, June) and quarters (Q4, Q1, Q2) for filtering?

Or, do you want to know that the contract will last 0 years, 6 months or 2 quarters? If you want to know how long the contract will last, would your example last 2 quarters, 3 quarters or 2.1 quarters?

Regards.

Generating Months, Quarters & Years between two dates

Tue, 04 Jan 2011 04:32:03 GMT

Firstly thank you for the reply Mr. Pover.

Yes I want to know the periods like Q1,Q2,Q3,Q4 and months and year between the contract period mentioned, so that I can show the data as number of days for that month/quarter/year as dimensions

Generating Months, Quarters & Years between two dates

Tue, 04 Jan 2011 08:47:58 GMT

Hi there,

I use the following script to do that, don't know if it's the best around. Difference is that I just need dates for de last 4 years, you'll need to assign the variables vStartDate and vEndDate in another way:

(In your fact table, make sure you have the factdate loaded as 'Date' (or the same fieldname as you'll use in the calendar table).

Let vToday=num(Today());

Let vStartDate = num(MakeDate(Year(ToDay()) - 4, 1, 1));

Let vEndDate = num(MakeDate(Year(ToDay()), 12, 31));

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

$(vStartDate)+IterNo()-1 as Num,

Date($(vStartDate)+IterNo()-1) as TempDate

AUTOGENERATE 1 WHILE $(vStartDate)+IterNo()-1<= $(vEndDate);

//*************** Master Calendar ******************

Calendar:

LOAD

TempDate as Date,

Year(TempDate) as Year,

Month(TempDate) as Month,

'Q' & Ceil(Month(TempDate)/3) as Quarter,

Week(TempDate) as Week

RESIDENT TempCalendar

ORDER BY TempDate Asc;

Regards,

Yves