Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
I am working on making some weekly report where in i have to consider Saturday as Weekstart & Friday as weekend for calculation. I am trying by setting FirstWeekDay in Script. But i am not able to the required result. Please advise
What is the script? What are you getting and what do you need?
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames=Sat;Sun','Mon;Tue;Wed;Thu;Fri;
Use FirstDayWeek and Last day in week functions. It will works.
Hi Arjun,
I tried by re-ordering week names, didn't work for me. Please can you share me the script
I didn't understand "use FirstDayWeek and Last day in week functions"
Hi Manoj, Can you share us your script? What is the output you are getting?
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='₹ #,##0.00;₹ -#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Sat;Sun;Mon;Tue;Wed;Thu;Fri';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-IN';
CalendarMaster_Temp:
LOAD
Date(Date) AS CalDate,
Date(Date) AS Date,
Year(Date) AS Year,
'Q' & Ceil(Month(Date) / 3) AS Quarter,
Month(Date) As Month,
Day(Date) As Day,
WeekDay(Date) AS WeekDay,
(if(month(weekend(Date))= month(Date),div(day(WeekEnd(Date)),7),div(day(Weekend(Date,-1)),7)+1)+1) as WkNo,
'W' & (if(month(weekend(Date))= month(Date),div(day(WeekEnd(Date)),7),div(day(Weekend(Date,-1)),7)+1)+1) as WeekNumber ,
Week(Date) As Week;
Load
Date(MinDate + IterNo() -1 ) AS Date // Replace Date with your actual date field name
While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
Min(Date) AS MinDate,
Max(Date) AS MaxDate // Replace Date with your actual date field name
RESIDENT Fact; /// Replace Invoice with your actual Transaction Table.
Calendar:
LOAD
CalDate,
Date,
Year,
Quarter,
Month,
Day,
WeekDay,
Week,
Date(WeekEnd(Date(Date,'DD-MM-YYYY'))-2,'DD-MMM-YYYY') as W_End,
Description, WeekNumber,
WkNo
Resident CalendarMaster_Temp;
DROP TABLE CalendarMaster_Temp;
SET DayNames='Sat;Sun;Mon;Tue;Wed;Thu;Fri';
This is working for me:
=WeekDay(WeekStart(Today()))
=WeekDay(WeekEnd(Today()))
This is working for me as well. I am looking for weekstart & weekend dates.
When I just use WeekStart(Today()), still it gives me Monday. How to get Saturday's date ?
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='₹ #,##0.00;₹ -#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Sat;Sun;Mon;Tue;Wed;Thu;Fri';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET FirstWeekDay=0;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-IN';
CalendarMaster_Temp:
LOAD
Date(Date) AS CalDate,
Date(Date) AS Date,
Year(Date) AS Year,
'Q' & Ceil(Month(Date) / 3) AS Quarter,
Month(Date) As Month,
Day(Date) As Day,
WeekDay(Date) AS WeekDay,
(if(month(weekend(Date))= month(Date),div(day(WeekEnd(Date)),7),div(day(Weekend(Date,-1)),7)+1)+1) as WkNo,
'W' & (if(month(weekend(Date))= month(Date),div(day(WeekEnd(Date)),7),div(day(Weekend(Date,-1)),7)+1)+1) as WeekNumber ,
Week(Date) As Week;
Load
Date(MinDate + IterNo() -1 ) AS Date // Replace Date with your actual date field name
While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
Min(Date) AS MinDate,
Max(Date) AS MaxDate // Replace Date with your actual date field name
RESIDENT Fact; /// Replace Invoice with your actual Transaction Table.
Calendar:
LOAD
CalDate,
Date,
Year,
Quarter,
Month,
Day,
WeekDay,
Week,
Date(WeekEnd(Date(Date,'DD-MM-YYYY'))-2,'DD-MMM-YYYY') as W_End,
Description, WeekNumber,
WkNo
Resident CalendarMaster_Temp;
DROP TABLE CalendarMaster_Temp;
For me those two are working. Please check.