Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I'm new to Qlik so bear with me. Let me know if I should refer this to another group.
I've been asked to create a dashboard containing charts and tables for a selected quarter and for some charts and tables for the selected quarter and the previous two quarters. However the quarters may be non-standard, e.g. September 2016 - November 2016, as supposed to something like October 2016 - December 2016. I have the data loaded and was able to obtain a master calendar from a colleague which is working correctly and is below:
MasterCalendar:
Load TempDate as "DATE",
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
Weekday(TempDate) as WeekDay,
Dual('Q' & Ceil(Month(TempDate)/3) & ' ' & Year(TempDate), QuarterStart(Date(TempDate))) as Quarter,
QuarterStart(Date(TempDate)) as QuarterStartDate,
Date(monthstart(TempDate),'MMM-YYYY') as MonthYear,
Week(TempDate)&'-'&Year(TempDate) as WeekYear,
inyeartodate(TempDate, $(vToday), 0) * -1 as CurYTDFlag,
inyeartodate(TempDate, $(vToday), -1) * -1 as PreviousYTDFlag,
InMonthToDate(TempDate, $(vToday), 0) * -1 as CurrMonthFlag,
InMonthToDate(TempDate,$(vToday), -1) * -1 as PrevMonthFlag,
num(month(TempDate)) as MonthNumber,
num(month($(vToday))) as CurrMonth,
Year(TempDate)*12+num(month(TempDate)) as PeriodNumber
Resident TempCal
Order By TempDate ASC;
What I'd like is for the end user to be able to select a month (MMM-YYYY) from a listbox or something similar which I already have created such that this selection makes up the last month of the "quarter". So for example if I selected November 2016, then the selected quarter will be September 2016 - November 2016. I also need to obtain the previous two "quarters", (March-May, June-August in my example) from this selected month. I'd need to be able to reference one or all 3 of these "quarters" in the expressions for certain charts and tables.
Alternatively, what I could do is select the 9 months that I need for my analysis from a listbox and then I would require the following:
I'm open to any type of solution. I'll see if I can throw up a similar toy example as I wouldn't have permission to put up the real example.
Hi Lorcan,
A small addition to your master calendar will make this easy. Add a sequential month number field that doesn't reset at the end of a year. So if Dec 2016 is 67, Jan 2017 will be 68.
One way to do this would be
MasterCalendar:
Load
*,
Autonumber(Year & '|' & Month, 'MonthID') as MonthID;
Load TempDate as "DATE",
Week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
.
.
.
Resident TempCal
Order By TempDate ASC;
Now a bit of set analysis will be useful.
=sum({$<MonthID = {">=$(=-2+max(MonthID))"}, Month=, Year =>}Amount)
will do the calculation on the selected month (or the most recent if more than one selected) and also on the two preceding months.
Month=, Year = means that the expression won't be restricted to selections made in the Month and Year field. You may need to amend or add to these field names to suit your app.
Good luck
Andrew