Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My Customer have 48 Weeks by Year and 4 Weeks by Month :
Week 1 : from 1 to 7,
Week 2 : from 8 to 15,
Week 3 : from 16 to 22,
Week 4 : from 23 to end of Month.
Please how can I get the data for this Week till Today ?
For example to day = 21/08/2013
So, week today = from 16/08/2013 to 21/08/2013.
Thanks.
Hi,
You can use WeekStart and WeekEnd functions to get the range of from and to dates.
Please can u explan with more details ?
( Step by Step )
Thanks
Hi,
You need to create a logic to create a week for this scenario.
The logic is, you create an if statement where you check the date and then assign a number.
Say for example.
If(Day(Date) >=1 and Day(Date) <=7,1,
If(Day(Date) >=8 and Day(Date) <=15,2,
If(Day(Date) >=16 and Day(Date) <=22,3,4))) as WeekNumber.
Now this will give you 1 to 4 number for each of the Month.
Now you can write another logic.
If(num(Month(Date)) =1,WeekNumber,
If(num(Month(Date)) =2,WeekNumber+1,
If(num(Month(Date)) =3,WeekNumber+2,.... and so on.
First Try to implement this logic by yourself, if not then will send you the example. But I want you to try by yourself and learn
Regards,
Kaushik Solanki
Hi,
I am sorry, I missunderstand you. Kaushik has given best example for you.
Create the field for the week number (i suppose it's already done) :
if(Day(YourDate)<=7,1,if(Day(YourDate)<=14,2,if(Day(YourDate)<=21,3,4) as Week
If you don't have any future data in your model, only data till today, you can use this set analysis to have your "week -today" values :
{<Year = {$(=max({1}Year)}, Month = {$(=max({1}Month)}, Week = {$(=max({1}Week))}>}
if you have also future data :
{<Year = {$(=max({1}Year)}, Month = {$(=max({1}Month)}, Week = {$(=max({1}Week))}, Date = {"<=$(=today())"}>}
Creating a unique field for each distinct weeks in your datamodel by concatenating Year&Month&Week will ease these expression and could be useful to find directly the last week. A flag for past or future data could also avoid date format issue in the set analysis with "today()" function.
Hope it helps,
Thomas
Hi Kaushik,
when i right in the editor :
If(Day(DW.ISSUEDT) >=1 and Day(DW.ISSUEDT) <=7,1,
If(Day(DW.ISSUEDT) >=8 and Day(DW.ISSUEDT) <=15,2,
If(Day(DW.ISSUEDT) >=16 and Day(DW.ISSUEDT) <=22,3,4))) as WeekNumber,
its not working
error.
The best solution is to create a field named WeekNumber in the script. For that you can use the expression Kaushik posted:
MyTable:
Load
MyField1,
MyField2,
...etc,
If(Day(DW.ISSUEDT) >=1 and Day(DW.ISSUEDT) <=7,1,
If(Day(DW.ISSUEDT) >=8 and Day(DW.ISSUEDT) <=15,2,
If(Day(DW.ISSUEDT) >=16 and Day(DW.ISSUEDT) <=22,3,4))) as WeekNumber,
FROM MySource;