Dynamic Weeks

[Anonymous]

Kindly help me in the below logic. Any help is very useful

I have created a variable in which Weeks Starts from Friday and End at Thursday. I stuck on the below logic

Can you all please help me to implement the below logic in the Week:

1. Only last four weeks is shown in Dimension (4- Latest Week, 3- Previous Week…).
2. This pattern goes on even the Month changes. For E.g.: 29^th Jan’16 – 4^th Feb’16 is 4^th Week & 22 Jan'16 - 28 Jan'16 is 3 Week.

Let vMin = num(MakeDate(2014,02,01));

Let vMax = num(TODAY());

Load Date($(vMin) + RowNo() -1) as DATE,

     if(num(Weekday(Date($(vMin) + RowNo() -1)))>=4,

     Week(Date($(vMin) + RowNo() -1))+1,

     Week(Date($(vMin) + RowNo() -1))) as WEEK

AutoGenerate 1

while Date($(vMin) + RowNo() -1) < Date($(vMax));

Thanks a lot in advance.

Regards

Deepanshu

[sunny_talwar · Accepted solution]

May be this:

Let vMin = num(MakeDate(2014,02,01));

Let vMax = num(TODAY());

LOAD *,

  If(TEMP <= 4, 5 - TEMP, 0) as WEEK_NUM_4M_TODAY;

LOAD *,

  AutoNumber(Year(DATE)&WEEK) as TEMP;

LOAD Date($(vMax) - IterNo() + 1) as DATE,

     if(num(Weekday(Date($(vMax) - IterNo() + 1)))>=4,

     Week(Date($(vMax) - IterNo() + 1))+1,

     Week(Date($(vMax) - IterNo() + 1))) as WEEK

AutoGenerate 1

while Date($(vMax) - IterNo() + 1) >= Date($(vMin));

[Anonymous]

Thanks a lot sir. sunindia

This is exactly what I want!!..

Please guide me how to apply in master calendar. I want to make it dynamic.

The difference between your logic and my is DATE($(vMinDate) +  RowNo() -1 ) as Tempdate. I am using this as my whole calendar.

Please guide me how to implement the above logic in to this pattern.

Minmaxtemp:

Load

Min(Open_Date) as MinDate,

Max(Open_Date) as MaxDate

Resident THR;

Let vMinDate =  NUM(PEEK('MinDate',0 , 'Minmaxtemp'));

Let vMaxDate =  NUM(PEEK('MaxDate',0 , 'Minmaxtemp'));

Let vToday = $(vMaxDate);

CalTemp:

  LOAD

    DATE($(vMinDate) +  RowNo() -1 ) as Tempdate

AutoGenerate

  $(vMaxDate) - $(vMinDate) +1;

DROP TABLE Minmaxtemp;

Master:

  Load

    Tempdate as Open_Date,

      if(num(Weekday(Tempdate))>=4,Week(Tempdate)+1, Week(Tempdate)) as Week

Resident CalTemp

Order by Tempdate desc;

DROP TABLE CalTemp;

Thanks a lot in advance

Regards

Deepanshu

[sunny_talwar]

I guess give this a shot:

Minmaxtemp:

LOAD Min(Open_Date) as MinDate,

          Max(Open_Date) as MaxDate

Resident THR;

LET vMinDate =  Num(Peek('MinDate',0 , 'Minmaxtemp'));

LET vMaxDate =  Num(Peek('MaxDate',0 , 'Minmaxtemp'));

LET vToday = $(vMaxDate);

CalTemp:

LOAD Date($(vMax) - IterNo() + 1) as Tempdate

AutoGenerate 1

While ($(vMax) - IterNo() + 1) >= $(vMin);

DROP Table Minmaxtemp;

Master:

LOAD *,

          If(Temp <= 4, 5 - TEMP, 0) as WEEK_NUM_4M_TODAY;

LOAD *,

          AutoNumber(Year(Open_Date)&Week) as Temp;

LOAD Tempdate as Open_Date,

          If(Num(WeekDay(Tempdate)) >= 4, Week(Tempdate)+1, Week(Tempdate)) as Week

Resident CalTemp

Order by Tempdate desc;

DROP Table CalTemp;

You should be able to do this for CalTemp also, since you are doing a Order by Tempdate desc in Master table load:

CalTemp: 

LOAD Date($(vMin) + IterNo() - 1) as Tempdate

AutoGenerate 1

While $(vMin) + IterNo() -1 <= $(vMax);

Give them a shot and let me know if they work or not

[Anonymous]

Thanks a lot Sunny for your help and time 🙂 Regards Deepanshu

[sunny_talwar]

Not a problem. I am glad I was able to help