Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
if u could upload your sample application, i can assist u.
Swarup