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

[MayilVahanan]

Hi

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 ;