Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.
Thanks for the prompt response, Jagan! That did the trick; you made this a really great friday!
Hi Jonathan,
If you got the answer please close this thread by giving Correct Answer.
Regards,
Jagan.