topic Re: Predefine Date range in QlikView

Predefine Date range

Tue, 08 Oct 2013 06:41:42 GMT

Dear Community,

May I know how to define date range in the script?

Requirement:

Used Dimension =

Week1 - Week6,

Week2 - Week7,

Week3 - Week8,

...

Expression =

Sum(Sales)

The first bar in the chart will show sum of sales from week1-week6,

2nd bar will show sum of sales from week2-week7,

3rd bar will show sum of sales from week3-week8...

I tried with this script :

'W' & Ceil(week(TempDate)/6) As ROIWeek

but it is not working as desired, it gives me 6 weeks which is week1-week6, week7-week12, week13-week18...

Not suit the requirement.

The master calendar code is as shown below:

...

MasterCalendar:

LOAD TempDate AS [Date],

Date(TempDate,'DD/MM/YY') as ShortDate,

//week(TempDate) AS Week,

year(TempDate) AS Year,

month(TempDate) AS Month,

if(Month(TempDate)='Jan',1,

if(Month(TempDate)='Feb',2,

if(Month(TempDate)='Mar',3,

if(Month(TempDate)='Apr',4,

if(Month(TempDate)='May',5,

if(Month(TempDate)='Jun',6,

if(Month(TempDate)='Jul',7,

if(Month(TempDate)='Aug',8,

if(Month(TempDate)='Sep',9,

if(Month(TempDate)='Oct',10,

if(Month(TempDate)='Nov',11,

12 ))))))))))) as Month_Number,

day(TempDate) AS Day,

weekday(TempDate) AS WeekDay,

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

'Q' & Ceil(Month(TempDate)/3)&'-'&right(year(TempDate),2) AS QuarterYear,

date(monthstart(TempDate), 'MMM YY') AS MonthYear,

//week(TempDate)&'-'&right(Year(TempDate),2) AS WeekYear,

Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CueYTDFlag,

Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag

RESIDENT TempCalendar

ORDER BY TempDate Asc;

DROP Table TempCalendar,Temp;

Thanks and best regards,

Chanel

Re: Predefine Date range

Gysbert_Wassenaar — Tue, 08 Oct 2013 07:01:12 GMT

For that you'll need another table that links the week with every range it is part of. You'll create the week field in the master calendar and then add another table with the range and week number. That would something like this:

Weekranges:

load * inline [

Range, Week

Week1 - Week6, 1

Week1 - Week6, 2

...etc

Week1 - Week6, 6

Week2 - Week7, 2

Week2 - Week7, 3

...etc

Week2 - Week7, 7

..etc

];

Note, the inline load is just an example. You can create the table any way you want, for example from loading an excel file.

Re: Predefine Date range

Tue, 08 Oct 2013 07:16:01 GMT

Thanks for prompt response!

But it's kinda impossible for us to update the .qvw script every week.

Initially i think of :

'W' & Ceil(week(TempDate)/6) As ROIWeek

But it's not working according to requirement, it gives week1-6, 7-12...

Any idea like while loop or for loop that will automatically create a date range?

Best regards,

Chanel

Re: Predefine Date range

Gysbert_Wassenaar — Tue, 08 Oct 2013 07:25:32 GMT

You can probably generate it with something like

Ranges:

Load distinct Week, Week +1 - iterno() as RangeNumber

resident MasterCalendar

while Week + IterNo()-1 < Week+6 ;

Re: Re: Predefine Date range

MayilVahanan — Tue, 08 Oct 2013 07:38:19 GMT

Try like this

PFA

Hope it helps

Re: Predefine Date range

Tue, 08 Oct 2013 08:06:09 GMT

Awesome!

Many thanks to Mayil Vahanan Ramasamy and G Wassenaar!!!

One additional hint needed is how can I start the WeekDim from 6 instead of 1?

Load Distinct

Week(Date) as WeekDim,

Week(Date) + IterNo()-1 as Week

Resident MasterCalendar

while Week + IterNo()-1 <= Week+5 ;

Best regards,

Chanel

Re: Predefine Date range

CELAMBARASAN — Tue, 08 Oct 2013 08:15:11 GMT

Do you need like this? Please explain little more?

Load Distinct

Week(Date) + 5 as WeekDim,

Week(Date) + IterNo()-1 as Week

Resident MasterCalendar

while Week + IterNo()-1 <= Week+5 ;

Load Distinct

Week(Date) as WeekDim,

Week(Date) + IterNo()-1 as Week

Resident MasterCalendar

Where Week(Date) > 5

while Week + IterNo() <= Week + 5 ;

Re: Predefine Date range

Tue, 08 Oct 2013 08:40:34 GMT

Thanks for proposed solution! It helped to start the week at week6 but at the end, it will add another 5 weeks to actual week as well...

Load Distinct

Week(Date) + 5 as WeekDim,

Week(Date) + IterNo()-1 as Week

Resident MasterCalendar

while Week + IterNo()-1 <= Week+5 ;

for the 2nd solution, i think it is not allowed to use where and while at the same time, or there is additional syntax needed?

Load Distinct

Week(Date) as WeekDim,

Week(Date) + IterNo()-1 as Week

Resident MasterCalendar

Where Week(Date) > 5

while Week + IterNo() <= Week + 5 ;

Thanks and best regards,

Chanel

Re: Predefine Date range

MayilVahanan — Tue, 08 Oct 2013 09:44:25 GMT

Try like this

Load * where WeekDim > 5;

Load Distinct Week(Date) as WeekDim, Week(Date) + IterNo()-1 as Week

Resident Tablename

while Week + IterNo()-1 <= Week+5 ;

Re: Predefine Date range

Tue, 08 Oct 2013 10:34:04 GMT

Hi,

I tried to modify to :

Load Distinct Week(Date) +5 as WeekDim, Week(Date) + IterNo()-1 as Week

Resident MasterCalendar

while Week + IterNo()-1 <= Week+5 ;

Load * where WeekDim < Week-5;

But it is still not working, i can see the next 5 weeks which is not actual week.

The max week number is week 38, but i can see until week 44...

SOS...

Best regards,

Chanel

Re: Predefine Date range

MayilVahanan — Tue, 08 Oct 2013 10:41:01 GMT

Try like this

Temp:

Load Max(Week) AS MaxWeek Resident Tablename;

Let vMaxWeek = Peek('MaxWeek');

Drop Table Temp;

Load * where WeekDim > 5 and WeekDim < $(vMaxWeek);

Load Distinct Week(Date) as WeekDim, Week(Date) + IterNo()-1 as Week

Resident Tablename

while Week + IterNo()-1 <= Week+5 ;