topic Re: Variable for Month in QlikView

Variable for Month

NickBentley — Wed, 18 Aug 2021 22:19:02 GMT

Hi All,

I am trying to create a variable for shipping month which at times is different from the actual calendar month - i.e. if the first few days of April like the 1st and 2nd fall on a Thursday and Friday I still want these dates to be classed in the month of March.

In my expressions, I am using a vCurrentMonth variable which is built from month(today()) but this will give the wrong value in the instance above when the shipping month is different from the calendar month.

Any suggestions would be greatly appreciated.

Thanks,

Nick

Re: Variable for Month

edwin — Wed, 25 Aug 2021 00:12:09 GMT

you need to define the business rules. if the 1st of the month falls on a Tuesday is that counted towards the prior month? maybe your determining factor is when the 1st day of the week falls? or is it based on the number of days in the week - whichever is greater? either way you can use the week to test if the calendar date falls under the current month or prior month.

this sample assumes that any calendar date falling on the same week where sunday is prior month will be counted towards the prior month:

dates: load date, week(date) as week, if(weekday(date)='Sun',MonthStart(date)) as tmpSHIPMONTH, year(date) & '|' & Week(date) as Yearweek, weekday(date) as weekday, day(date) as day; load date(today()-iterno()) as date while iterno()<300; load 1 autogenerate(1); NoConcatenate weeks: load distinct tmpSHIPMONTH as SHIPMONTH, Yearweek Resident dates where not isnull(tmpSHIPMONTH); inner join (dates) load * resident weeks; drop table weeks; drop field tmpSHIPMONTH;

Re: Variable for Month

edwin — Wed, 25 Aug 2021 00:15:28 GMT

as you can see dec 1 to 5 fall under nov bec they are on the same week as the last of Nov which included the sunday for that week