Non-Standard Quarter Selection

[Anonymous]

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:

• The first and last month of the last "quarter to be concatenated into a quarter, e.g. Sep 16 - Nov 16, that would be inserted into a text object. I can live with Sep 16, Oct 16, Nov 16.
• For any charts or tables that only focuses on the latest "quarter" that the expression would contain a get field selection for the last three months selected.
• Similar to the above, for any charts or tables that focuses on comparisons between the three selected "quarters" from the 9 months selected that expressions for the oldest "quarter" would contain a get field selection for the oldest 3 months, expressions for the middle "quarter" would contain a get field selection for the middle 3 selected months and expressions for the latest "quarter" would contain a get field selection for the latest 3 selected months.

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.

[effinty2112]

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