Custom List Box with only Last Week, Last Month, Last Quarter, Last Year, and All Data

[#1]

I've been asked to create a list box with only five values in it to show: Last week, Last Month, Last Quarter, Last Year, and All Data. And those are the only selections they to be included in the list (I've already created a multi box, but this person wants a list of only these five often-visited selections).

How in the ham sandwich might I do that? I already have a fields for Week, Month, Quarter and Year.

Thanks again for your time!

[#2]

Just a sugestion....

On your script....load last week,month,quarter,year and date...and put in a new table....and link this table im your model.....

[johnw]

Download Rob Wunderlich's QlikView cookbook:

http://robwunderlich.com/Download.html

In it is an example called dategrouping.qvw. It does what you want by setting up date ranges and then interval matching the ranges back to your dates - basically exactly what Érico suggested if I understood the suggestion.

[#4]

Thanks for the link, John. Unfortunately, I'm using the personal edition of Qlikview and am therefore unable to open the file from Rob's site. Could I trouble you for a screenshot to the relevant code?

Thanks for your time!

[johnw · Accepted solution]

Below is the script. To see it work, create a list box for Range and one for Date. Selections in Range affect what Dates are possible because of the connection between the two tables.

/* This section just creates some test data */
data:
LET vEndDate = num(today(1)); // End at today's date
LET vStartDate = num(addmonths(today(1),-14)) -1; // Start at 14 months earlier than today
TestData:
LOAD date($(vStartDate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vStartDate) + IterNo() <= $(vEndDate)
;
/* End of test data */
/* This script demonstrates assigning Dates to named groups, such as "Yesterday" or "Last Week".
Date table was created on the previous tab.
*/
// Assign current date to a variable. Makes INLINE below a little neater and ensures we don't cross midnight boundry.
LET vToday=today(1);

Ranges:
// Use the "evaluate()" function to execute the input function text and convert it to actual date values.
LOAD Range, date(evaluate(RangeStart)) as RangeStart, date(evaluate(RangeEnd)) as RangeEnd
;
/* The INLINE data defines the Range Name, Starting date of the Range, and Ending date of the Range.
The Start/End values are defined using standard QV date functions.
Semicolon is used for a field delimeter because some of the functions contain commas.
*/
LOAD * INLINE [
Range; RangeStart; RangeEnd
Today; vToday; DayEnd(vToday)
Yesterday; vToday-1; DayEnd(vToday-1)
This Week; WeekStart(vToday); WeekEnd(vToday)
Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1)
Last 2 Weeks; WeekStart(today(1),-1); WeekEnd(today(1))
This Month; MonthStart(vToday); MonthEnd(vToday)
Last Month; MonthStart(vToday,-1); MonthEnd(vToday,-1)
Last Two Months; MonthStart(vToday,-1); MonthEnd(vToday)
This Year; YearStart(vToday); YearEnd(vToday)
Last Year; YearStart(vToday,-1); YearEnd(vToday,-1)
] (delimiter is ';')
;

/*
Use IntervalMatch to link the Date field into multiple Range fields.
Doing a JOIN avoids creating a messy synthetic key.
*/
JOIN (Ranges) IntervalMatch (Date) LOAD RangeStart, RangeEnd RESIDENT Ranges;
// Because we did a JOIN, we may drop the the Start/End fields.
DROP FIELDS RangeStart, RangeEnd;

[#6]

Thanks so much for sharing this script, John. It worked like a charm!

[#7]

I was able to load the Range list box, but none of my charts update when i make a selection from it. I dont have any set analysis in my expressions that would prevent this. Any ideas/suggestions on how to fix it?

Thanks

[camilo]

In this same example of date grouping, there is a way to group last FRI-SAT-SUN ?

Thanks,

Camilo

[rajtechnocraft]

Hi John,

I used the logic which u have provided in the script and it is working fine

I want minor customisation in it for Last 2 Months,Last 2 weeks

In This suppose my calender is

Year                  Month

2013                   Jan

2013                    Feb

2013                    Mar

Last 2 months it should show me data for 2013 Jan and Feb months only.It should not include March

Similarly for Last 2 weeks it shold show me data from 10th Mar 2013 to 24mar 2013  and it should not include current week.25Mar to 28mar 2013

Similarly for Last 2 quarters.

i require this help desperately

Could you please help me on this

Regards

Raj

[rajtechnocraft]

Original code shared by John/Rob Below is the script. To see it work, create a list box for Range and one for Date. Selections in Range affect what Dates are possible because of the connection between the two tables. /* This section just creates some test data */ data: LET vEndDate = num(today(1)); // End at today's date LET vStartDate = num(addmonths(today(1),-14)) -1; // Start at 14 months earlier than today TestData: LOAD date($(vStartDate) + IterNo()) as Date AUTOGENERATE 1 WHILE $(vStartDate) + IterNo() <= $(vEndDate) ; /* End of test data */ /* This script demonstrates assigning Dates to named groups, such as "Yesterday" or "Last Week". Date table was created on the previous tab. */ // Assign current date to a variable. Makes INLINE below a little neater and ensures we don't cross midnight boundry. LET vToday=today(1); Ranges: // Use the "evaluate()" function to execute the input function text and convert it to actual date values. LOAD Range, date(evaluate(RangeStart)) as RangeStart, date(evaluate(RangeEnd)) as RangeEnd ; /* The INLINE data defines the Range Name, Starting date of the Range, and Ending date of the Range. The Start/End values are defined using standard QV date functions. Semicolon is used for a field delimeter because some of the functions contain commas. */ LOAD * INLINE [ Range; RangeStart; RangeEnd Today; vToday; DayEnd(vToday) Yesterday; vToday-1; DayEnd(vToday-1) This Week; WeekStart(vToday); WeekEnd(vToday) Last Week; WeekStart(vToday,-1); WeekEnd(vToday, -1) Last 2 Weeks; WeekStart(today(1),-1); WeekEnd(today(1)) This Month; MonthStart(vToday); MonthEnd(vToday) Last Month; MonthStart(vToday,-1); MonthEnd(vToday,-1) Last Two Months; MonthStart(vToday,-1); MonthEnd(vToday) This Year; YearStart(vToday); YearEnd(vToday) Last Year; YearStart(vToday,-1); YearEnd(vToday,-1) ] (delimiter is ';') ; /* Use IntervalMatch to link the Date field into multiple Range fields. Doing a JOIN avoids creating a messy synthetic key. */ JOIN (Ranges) IntervalMatch (Date) LOAD RangeStart, RangeEnd RESIDENT Ranges; // Because we did a JOIN, we may drop the the Start/End fields. DROP FIELDS RangeStart, RangeEnd; Hi John, I used the logic which u have provided in the script and it is working fine I want minor customisation in it for Last 2 Months,Last 2 weeks In This suppose my calender is Year                  Month 2013                  Jan 2013                    Feb 2013                    Mar Customisation Requiredin above code to meet the below requirements: Last 2 months it should show me data for 2013 Jan and Feb months only.It should not include March Similarly for Last 2 weeks it shold show me data from 10th Mar 2013 to 24mar 2013  and it should not include current week.25Mar to 28mar 2013 Similarly for Last 2 quarters. i require this help desperately Could you please help me on this Regards Raj