topic Re: Need Help with Creating Start and End Date Variables in QlikView

Need Help with Creating Start and End Date Variables

Tue, 22 Nov 2011 18:31:59 GMT

So I've spent way too much time trying to search for this and am getting nowhere. Exactly HOW do I set up start and end date variables for use with a slider/calendar object? What I need is for the end user to be able to select a start date, then an end date, and results will return within those dates. I can do this in SSRS just fine, but with Qlikview, not so much.

Folks, I need a step-by-step which a newbie can understand. I need this ASAP. If I sound frustrated then you're correct - I don't have all day to figure this out and nothing I've tried works. There's nothing about this anywhere in the tutorial that I can find, and i'm to the point where I just need results. There needs to be a place where this is listed somehow.

Can someone list the steps, or at least point me in the direction of a very good resource which will make sense to a newbie?

Re: Need Help with Creating Start and End Date Variables

swarup_malli — Tue, 22 Nov 2011 19:41:21 GMT

Hey,

Don't get frustated,you have posted ur doubts at the right place, Ppl here are very helpful. Its a very simple prob, and has a very simple solution.

Which every QV developer solves by creating a master table.You find a master table in every QV application, if u check any sample QV application u can find on the QV website u'll see it , u'll find it in the script editor.

1st u have a create a temp table to find min and max date .Once that is found, u can auto generate the dates b/w min and max.

Let me paste a sample here

Temp:

Load

MIN(yourdatefield) as minDate,

MAX(yourdatefield) as maxDate,

RESIDENT

yourtablewhich_contains_the_date_value;

Let vMinDate = NUM(PEEK('minDate',0,'Temp'));

Let vMaxDate = NUM(PEEK('maxDate',0,'Temp'));

//Let vCurrentCamp = NUM(PEEK('maxCampYear',0,'Temp'));

DROP TABLE Temp;

//varMinDate to varMaxDate

TempCalendar:

LOAD

$(vMinDate)+IterNo()-1 AS Num,

Date($(vMinDate)+IterNo()-1) AS TempDate

AUTOGENERATE 1 WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

//Building the master calendar with most date dimensions

MasterCalendar:

LOAD

TempDate AS TRANSACTIONDATE,

Year(TempDate) as Year,

// 'Q'& CEIL(MONTH(TempDate)/3) as QUARTER,

Month(TempDate) as Month,

pick(num#(text(Date(TempDate,'M'))),9,10,11,12,1,2,3,4,5,6,7,8) as MonthOrder,

DUAL(text(Month(TempDate)),pick(num#(text(Date(TempDate,'M'))),9,10,11,12,1,2,3,4,5,6,7,8)) as MonthName,

// week(TempDate) as Week,

DUAL(weekstart(TempDate,0,-1) & ' - ' & weekend(TempDate,0,-1),weekstart(TempDate,0,-1)) AS Week,

Day(TempDate) as Day,

// InYearToDate(TempDate,'$(vToday)',0) AS CurYearToDate,

// InQuarterToDate(TempDate,'$(vToday)',0) AS CurQuarterToDate,

// InMonthToDate(TempDate,'$(vToday)',0) AS CurMonthToDate,

// InYearToDate(TempDate,'$(vLastYearToday)',0) AS LastYearToDate,

// InQuarterToDate(TempDate,'$(vLastYearToday)',0) AS LastQuarterToDate,

// InMonthToDate(TempDate,'$(vLastYearToday)',0) AS LastMonthToDate,

// 'Q'& CEIL(MONTH(TempDate)/3)&'-'&Year(TempDate) AS QuarterYear,

date(monthstart(TempDate),'MMM-YYYY') AS MonthYear

// Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

// weekday(TempDate) as WeekDay

RESIDENT

TempCalendar

ORDER BY

TempDate Asc;

DROP TABLE TempCalendar;

Re: Need Help with Creating Start and End Date Variables

swarup_malli — Tue, 22 Nov 2011 19:43:49 GMT

check this post, is very helpful

http://community.qlik.com/message/162686#162686

Swarup

Re: Need Help with Creating Start and End Date Variables

swarup_malli — Tue, 22 Nov 2011 19:50:50 GMT

if u could upload your sample application, i can assist u.

Swarup