How to make straight table only show values with in selected date range?

[#1]

Hi,

I have a straight table in qvw. when user selects date range. the user wants to pull data only with in selected date range. The table is pulling all values including date range selections data and blanks. but the user wants to see only data in the table based on date range selections. how to make the table show only data within the selected date range?

the table have below

Dimension 1: =If(Match(CODE ,'IN','OUT'),ENTRYID)

Dimension 2: Order

Expr1: =Only(Name)

Expr2: =Only(StafdMember)

Expr3: =Max(ConnectionDate)

Expr4:  =FirstSortedValue(BOOKTEXT,-ConnectionDate)

Date selections have below Triggers in OnChange

vStartDate: ='>='&vStartDate & '<=' & vEndDate

vEndDate  : ='>='&vStartDate & '<=' & vEndDate

Please can anyone suggest me.

Thanks.

[buzzy996]

i think the problem with ur calendar objects with triggers,can u try with the following variables..

start date calender date object:

vStartDate

=date(min(All urDateField))   -----Min Value

=date(max(All urDateField)+1) ---Max Value

End date calender date object:

VEndDate

=$(vStartDate)

=DATE(MAX(ALL urDateField))

-------------------------------------------

Document Event Triggers:

OnAnySelect

Set Variable

vStartDate

=if(len(GetCurrentSelections())=0,null(),vStartDate)

set variable

vEndDate

=if(len(GetCurrentSelections())=0,null(),vEndDate)

OnOpen

set variable

vStartDate

set variable

vEndDate

=Today()

Variable event triggers

vStartDate onchage

ClearAll

Select in Field

DayMonthYear

='=DayMonthYear >= vStartDate and DayMonthYear <= vEndDate'

VEndDate

ClearAll

Select in Field

DayMonthYear

='=DayMonthYear >= vStartDate and DayMonthYear <= vEndDate'

[#3]

You have to modify all these expression to accommodatevStartDate and vEndDate

[#4]

Thanks. Sorry I confused where I need to add the first ones which shows below

vStartDate

=date(min(All DayMonthYear))

=date(max(All DayMonthYear)+1)

VEndDate

=$(vStartDate)

=DATE(MAX(ALL DayMonthYear))

[#5]

Thanks. how to modify the expressions? let me know please.

[buzzy996]

look at my first post again,modified a bit where u have to apply those syntaxes.

[#7]

sorry, do you mean do I need to change here?

//--------------------------------------------------------------------------------------
// Generate Master Calendar
//--------------------------------------------------------------------------------------

TRACE  Creating Master Calendar;

/* Load transaction TABLE to get earliest and latest dates */

MinMax:
LOAD
     Max(TIMESTAMP(DATE+10)) AS MaxDate,
     Min(TIMESTAMP(DATE)) AS MinDate

FROM
[..\QVD\DATA.qvd]
(qvd);

//EXIT SCRIPT;
/* STORE earliest and latest dates in variables */

LET varMinDate = Num(Peek('MinDate', -1, 'MinMax'));
LET varMaxDate = Num(Peek('MaxDate', -1, 'MinMax')+1); 

// EXIT SCRIPT;
/* STORE todays date in variable */

LET varToday = Num(Today());
 
/* Inline TABLE mapping months to quarters */

QuarterMap:
MAPPING LOAD * INLINE [
     Month, Quarter
     1, Q1
     2, Q1
     3, Q1
     4, Q2
     5, Q2
     6, Q2
     7, Q3
     8, Q3
     9, Q3
     10, Q4
     11, Q4
     12, Q4
];


/* Autogenerate every date between varMinDate and varMaxDate while creating calendar fields */


MasterCalendar:
LOAD*,
  FiscalYear  & '/ W'  & FiscalWeek AS  FiscalYearWeek; 
LOAD*,
  YearStart(Date,0,7) AS FiscalYearStart,
  YearName(Date,0,7) AS FiscalYear,
  AUTONUMBER(Year(Date) & Week(Date)) AS FiscalWeekSerial,
  ROUND(num(([Date]+184-(MakeDate(Year(([Date]+184)-mod(([Date]-2),7)+3), 1, (mod(([Date]-2),7)+1) )-10))/7-0.5,'#,##0')) AS FiscalWeek,
  'Q' & CEIL(if(num(Month(Date))<7,num(Month(Date))+6,num(Month(Date))-6)/3) AS FiscalQuarter;
LOAD
 
  Timestamp(Date) as Date_TS,
  Date#(Date(Date)) AS Date,
  IF(Date = Today(),1) AS Today,
  week(Date) AS Week,
  Year(Date) AS Year,
  Month(Date) AS Month,
  Day(Date) AS Day,
  WeekDay(Date) AS WeekDay,
  ApplyMap('QuarterMap', Num(Month(Date)), Null()) AS Quarter,
  Date(MonthStart(Date),'MMM-YYYY') AS MonthYear,
  Week(Date) & '-' & Year(Date) AS WeekYear,
  InYearToDate(Date, $(varToday), 0) * -1 AS CurYTDFlag,
  InYearToDate(Date, $(varToday), -1) * -1 AS LastYTDFlag;  
LOAD
  Date($(varMinDate) + RecNo() - 1) AS Date
AUTOGENERATE ($(varMaxDate) - $(varMinDate)+1);       

/* DROP Temporary TABLE */

DROP TABLE MinMax;


STORE MasterCalendar INTO C:\Qlikview\QVD\MasterCalendar.qvd;

DROP TABLE MasterCalendar;
 

[avinashelite]

hey try it in set analysis its simple and effective , Try as below:

count({<Calender_Date={">=$(=Date(vStartDate,'MM/DD/YYYY'))<=$(=Date(vEndDate,'MM/DD/YYYY'))"}>}CR_ID)

Create the variable to read the start date and end date, then use the above expression:

Calender_Date is the date column where you want the range need to be selected .

Hope this helps you.

[buzzy996]

no..,from front end i think so u have calender objects,u have modify by clicking those objects properties and "Document Event Triggers"  sytax through Documents properties--trigger tab of ur qvw

if possible share ur app,we try to help.

note:don't change any script from edit script.

[#10]

I don't have any calendar date objects like start and end dates. all I have is in Variable box vStartDate and vEndDate. do I need to use below in variable box?

start date calender date object:

vStartDate

=date(min(All urDateField))   -----Min Value

=date(max(All urDateField)+1) ---Max Value

End date calender date object:

VEndDate

=$(vStartDate)

=DATE(MAX(ALL urDateField))