Week Calculation

[manojkulkarni]

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

[sunny_talwar]

What is the script? What are you getting and what do you need?

[qlikviewwizard]

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.

[manojkulkarni]

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"

[qlikviewwizard]

Hi Manoj, Can you share us your script? What is the output you are getting?

[manojkulkarni]

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;       

[qlikviewwizard]

SET DayNames='Sat;Sun;Mon;Tue;Wed;Thu;Fri';

This is working for me:

=WeekDay(WeekStart(Today()))

=WeekDay(WeekEnd(Today()))

[manojkulkarni]

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 ?

[manojkulkarni]

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;      

[qlikviewwizard]

For me those two are working. Please check.