topic Re: Week creation in QlikView

Week creation

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

Hi,

I need to create an week and i get this by week(r_date) as week and it gave weeks from (1 to 53) but i need an week as per month 5 weeks like if user select March means it should display w1,w2,w2,w4,w5 like i need to show.

Thanks....

Re: Week creation

nareshthavidishetty — Fri, 11 Mar 2016 09:13:49 GMT

Issue got resolved.

Logic:'Week' & Ceil(Day(Date)/7) AS WeekInMonth

Thanks

Re: Week creation

Kushal_Chawda — Fri, 11 Mar 2016 09:17:45 GMT

Please mark the correct answer and close the thread

Re: Week creation

nareshthavidishetty — Fri, 11 Mar 2016 09:36:37 GMT

Hi,

Am having an issue after using the below logic

'Week' & Ceil(Day(Date)/7) AS WeekInMonth

am getting result as

which is not correct if we check with jan calender week1 having only 1 & 2 but here it shows 1 to 7 .please help me to get this.

Thanks..

Re: Week creation

MarcoWedel — Fri, 11 Mar 2016 09:50:17 GMT

‌please define the logic to count the weeks per month.

thanks

regards

Marco

Re: Week creation

kaushiknsolanki — Fri, 11 Mar 2016 10:11:09 GMT

Hi,

Check this attachment.

Regards,

Kaushik Solanki

Re: Week creation

nareshthavidishetty — Fri, 11 Mar 2016 10:22:49 GMT

Hi Kaushik,

Below is the O/P getting in the attached application.Please check the system calender with JAN-16,week1 and compare that with below which is not correct.So my requirement is if user select Jan-16,week1 the date should reflect as 01-01-2016 and 01-02-2016 which is comes under week1

Thanks..

Re: Week creation

Fri, 11 Mar 2016 10:38:40 GMT

Hi Naresh,

check with the below logic

'w'&ceil(day(Now())/7)

instead of now() replace with your column name.

Sub2u444‌

Re: Week creation

ThornOfCrowns — Fri, 11 Mar 2016 10:50:29 GMT

Try the answers here:

Monthly week number !

Re: Week creation

MarcoWedel — Fri, 11 Mar 2016 14:24:02 GMT

Hi,

maybe one solution could be:

'W'&AutoNumber(WeekName(Date),MonthName(Date)) as MonthWeek

one example:

table1:
LOAD *,
     WeekDay(Date) as WeekDay,
     WeekName(Date) as WeekName,
     MonthName(Date) as MonthName,
     'W'&AutoNumber(WeekName(Date),MonthName(Date)) as MonthWeek;
LOAD Date(MakeDate(2015)+IterNo()-1) as Date
AutoGenerate 1
While MakeDate(2015)+IterNo()-1 <= Today();

hope this helps

regards

Marco

Re: Week creation

nareshthavidishetty — Fri, 11 Mar 2016 14:30:02 GMT

Thanks..Issue got resolved below is the script

LOAD *,

Dual('Q' & Ceil(FIMonth/3), Ceil(FIMonth/3)) AS FQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(new_order_date), 'MMM')), FIMonth) AS Month, // Fiscal Calendar Month Name

'Week' & pick(match([WeekNumber],0,1,2,3,4),1,2,3,4,5) as Week;

LOAD *,

Year(new_order_date) AS Years, // Standard Calendar Year

Month(new_order_date) AS Months,

day(new_order_date) as Day,

if(month(weekend(new_order_date,0,6))= month(new_order_date),div(day(WeekEnd(new_order_date,0,6)),7),div(day(Weekend(new_order_date,-1,6)),7)) as WeekNumber,

Date(MonthEnd(new_order_date), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(new_order_date)/3), Ceil(Month(new_order_date)/3)) AS Quarter, // Standard Calendar Quarter

Mod(Month(new_order_date) - $(vFiscalYearStartMonth), 12)+1 AS FIMonth, // Fiscal Calendar Month

YearName(new_order_date, 0, $(vFiscalYearStartMonth)) AS FIYear; // Fiscal Calendar Year

LOAD Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS new_order_date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;