Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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!

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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;

View solution in original post

9 Replies
Not applicable
Author

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
Champion III
Champion III

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.

Not applicable
Author

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
Champion III
Champion III

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;

View solution in original post

Not applicable
Author

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

Not applicable
Author

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
Contributor III
Contributor III

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

Thanks,

Camilo

rajtechnocraft
Contributor III
Contributor III

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
Contributor III
Contributor III

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