3 Replies Latest reply: Oct 25, 2015 5:39 AM by jagan mohan rao appala RSS

    Defining calendar weeks with names

      Hi folks,

       

      Very new Qlik Sense user here (IT guy, now tasked to BI). I really like Qlik, but I'm trying to puzzle through stuff on my own. Currently using Qlik Sense Enterprise 1.1, though I'm planning on upgrading to 2.x soon, once this project is finished.

       

      A problem I'm facing is that we track sales on a weekly basis, with sales meetings on tuesdays. However, we also need to look at it by month. Take this month, October, for example. Because the 1st of October is on a thursday, I would have a bar plot with values for the following 'bins':

       

      Oct. 1 - Oct. 2

      Oct. 5 - Oct. 9

      Oct. 12 - Oct. 16

      Oct. 19 - Oct. 26

      Oct. 26 - Oct. 30

       

      So, I guess I have two problems:

       

      1) I need to be able to define the date ranges for plotting, and I'm not at all sure how to go about this.

      2) I need to have the dates display as above (Or at least in a readable format for users of the data)

       

      I've been through other forum posts and haven't had any luck with the few other solutions I've found. I suspect that the calendar I'm using is a bit too much of a hodgepodge, coupled with a poor understanding of the date functions on my part, so I've included it here. Our fiscal year starts in August, and "Posting Date" is what all our sales transactions are linked by.

       

      Thanks in advance for any help; I'm still learning this stuff.

       

      Jonathan

       

      // Begin Master Calendar

       

      SET vFiscalYearStartMonth = 8;

      LET vStartDate = Num(YearStart('2012-08-01'));

      LET vEndDate = Num(YearEnd(Today()));

       

      FiscalCalendar:

      LOAD

      *,

      Date as "Posting Date",

      Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

      Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

      LOAD

      *,

      Year(Date) AS Year, // Standard Calendar Year

      Month(Date) AS Month, // Standard Calendar Month

      Week(Date) as Week,

      Day(Date) as Day,//

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

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

      Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

      YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear,  // Fiscal Calendar Year

      WeekName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalWeek,

       

       

      Dual(Year(Date) & '-Q' & Ceil(Month(Date)/3), Year(Date) & Ceil(Month(Date)/3)) as YearQtr,

      Date(Monthstart(Date), 'MMM-YYYY') as YearMonth,

      //WeekStart(date(Week(Date)))& '-'  &WeekEnd(date(Week(Date))) as SalesWeek;

      Date(WeekName, 'DD MMM YYYY') & ' - ' & Date(weekend(WeekName), 'DD MMM YYYY');

       

       

      LOAD

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

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

      AutoGenerate vEndDate - vStartDate + 1;