Solved: Week creation - Qlik Community

nareshthavidishetty · ‎2016-03-11

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....

nareshthavidishetty · ‎2016-03-11

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;

View solution in original post

nareshthavidishetty · ‎2016-03-11

Issue got resolved.

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

Thanks

Kushal_Chawda · ‎2016-03-11

Please mark the correct answer and close the thread

nareshthavidishetty · ‎2016-03-11

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..

MarcoWedel · ‎2016-03-11

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

thanks

regards

Marco

kaushiknsolanki · ‎2016-03-11

Hi,

Check this attachment.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

nareshthavidishetty · ‎2016-03-11

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..

Report Inappropriate Content · ‎2016-03-11

Hi Naresh,

check with the below logic

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

instead of now() replace with your column name.

Sub2u444‌

ThornOfCrowns · ‎2016-03-11

Try the answers here:

Monthly week number !

MarcoWedel · ‎2016-03-11

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

Week creation

Related Topics