Predefine Date range

[#1]

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

[Gysbert_Wassenaar]

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.

[#3]

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

[Gysbert_Wassenaar]

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 ;

[MayilVahanan · Accepted solution]

Hi

Try like this

PFA

Hope it helps

[#6]

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

[CELAMBARASAN]

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 ;

or

Load Distinct

    Week(Date) as WeekDim,

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

Resident MasterCalendar

Where Week(Date) > 5

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

[#8]

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

[MayilVahanan]

Hi

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 ;

[#10]

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