Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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;

3 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

LOAD

*

Date(If(Month(WeekStart(Date)) <> Month(Date), MonthStart(Date), WeekStart(Date)), 'MMM. DD') & '-' &

Date(If(Month(WeekEnd(Date)) <> Month(Date), MonthEnd(Date), WeekEnd(Date)), 'MMM. DD') AS MonthWeek

FROM DataSource;

Regards,

Jagan.

Not applicable
Author

Thanks for the prompt response, Jagan! That did the trick; you made this a really great friday!

jagan
Luminary Alumni
Luminary Alumni

Hi Jonathan,

If you got the answer please close this thread by giving Correct Answer.

Regards,

Jagan.