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

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

Thu, 19 Aug 2010 19:16:09 GMT

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!

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

Thu, 19 Aug 2010 19:24:43 GMT

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.....

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

johnw — Thu, 19 Aug 2010 19:38:09 GMT

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.

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

Thu, 19 Aug 2010 21:41:13 GMT

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!

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

johnw — Thu, 19 Aug 2010 21:54:31 GMT

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;

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

Fri, 20 Aug 2010 23:57:49 GMT

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

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

Wed, 08 Dec 2010 21:15:35 GMT

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

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

camilo — Fri, 19 Aug 2011 15:21:52 GMT

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

Thanks,

Camilo

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

rajtechnocraft — Wed, 27 Mar 2013 06:00:46 GMT

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

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

rajtechnocraft — Wed, 27 Mar 2013 06:09:57 GMT

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