topic Re: Week Calculation in QlikView

Week Calculation

manojkulkarni — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Week Calculation

sunny_talwar — Thu, 24 Aug 2017 01:48:16 GMT

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

Re: Week Calculation

qlikviewwizard — Thu, 24 Aug 2017 01:57:30 GMT

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.

Re: Week Calculation

manojkulkarni — Thu, 24 Aug 2017 02:07:12 GMT

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"

Re: Week Calculation

qlikviewwizard — Thu, 24 Aug 2017 02:12:52 GMT

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

Re: Week Calculation

manojkulkarni — Thu, 24 Aug 2017 02:17:05 GMT

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;

Re: Week Calculation

qlikviewwizard — Thu, 24 Aug 2017 02:21:08 GMT

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

This is working for me:

=WeekDay(WeekStart(Today()))

=WeekDay(WeekEnd(Today()))

Re: Week Calculation

manojkulkarni — Thu, 24 Aug 2017 02:26:50 GMT

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 ?

Re: Week Calculation

manojkulkarni — Thu, 24 Aug 2017 02:28:07 GMT

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;

Re: Week Calculation

qlikviewwizard — Thu, 24 Aug 2017 02:35:07 GMT

For me those two are working. Please check.

Re: Week Calculation

manojkulkarni — Thu, 24 Aug 2017 02:59:36 GMT

=(WeekStart(Today())) is returning 21-08-2017 where as Saturday is 19-08-2017

Re: Week Calculation

Anil_Babu_Samineni — Thu, 24 Aug 2017 03:02:41 GMT

Try this in your environment

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

Re: Week Calculation

prma7799 — Thu, 24 Aug 2017 03:07:27 GMT

Hi Manoj ,

Your script is ok.

Instead of this WeekStart(Today()) try this function WeekDay(WeekStart(Today()))

Re: Week Calculation

qlikviewwizard — Thu, 24 Aug 2017 03:37:56 GMT

This is working for me:

=WeekDay(WeekStart(Today()))

=WeekDay(WeekEnd(Today()))

Re: Week Calculation

manojkulkarni — Thu, 24 Aug 2017 06:23:00 GMT

I agree, by using weekday() function it is returning the value. But i would need the weekstart & end dates for my calculation.

Re: Week Calculation

chris_johnson — Thu, 24 Aug 2017 09:59:04 GMT

If I'm reading it right you may just need to change the line of script where it says:

SET FirstWeekDay=0;

SET FirstWeekDay=5;