Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikwiz123
Creator II
Creator II

Date selections on Multiple Date fields

I have a table with multiple date fields

 

DateA,

DateB,

DateC,

DateD

I need to create 2  filters - 

1st Filter - 'Yesterday', 'Last 7 Days', 'Last 10 Days', 'Last 14 Days', 'Last 30 Days'.

2nd Filter -  'Today', 'Yesterday', 'This Week', 'Last Week', 'Last Two Weeks', 'This Month', 'Last Month', 'Last Two Months', 'This Year', 'Last Year',

When I select any value, that should apply on all date fields. 

Labels (2)
8 Replies
Saravanan_Desingh

May be you can build a Calendar like this.

CalRules:
LOAD * INLINE [
    Name, Rule
    Today, =Today()
    Yesterday, "=Today()-1"
    Last 7 Days, ">=Today()-7"
    Last 10 Days, ">=Today()-10"
    Last 14 Days, ">=Today()-14"
    Last 30 Days, ">=Today()-30"
];

tab1:
LOAD Date(Today()-IterNo()+1) As tranDate
AutoGenerate 1
While IterNo() <=45
;
Left Join(tab1)
LOAD *
Resident CalRules;

tab2:
LOAD *, Evaluate(Chr(39)&tranDate&Chr(39)&Rule) As Eval, Chr(39)&tranDate&Chr(39)&Rule As Str
Resident tab1;

tab3:
NoConcatenate
LOAD *
Resident tab2
Where Eval;

Drop Table CalRules, tab1, tab2;

commQV47.PNG

Saravanan_Desingh

commQV47.PNG

Saravanan_Desingh

CalRules:
LOAD * INLINE [
    Name, Rule1, Rule2
    Today, =Today()
    Yesterday, "=Today()-1"
    Last 7 Days, ">=Today()-7"
    Last 10 Days, ">=Today()-10"
    Last 14 Days, ">=Today()-14"
    Last 30 Days, ">=Today()-30"
    This Week, ">=WeekStart(Today())"
    Last Week, ">=WeekStart(Today(),-1)", "<=WeekEnd(Today(),-1)"
    Last Two Weeks, ">=WeekStart(Today(),-2)", "<=WeekEnd(Today(),-2)"    
    This Month, ">=MonthStart(Today())"
    Last Month, ">=MonthStart(Today(),-1)", "<=MonthEnd(Today(),-1)"
    Last Two Months, ">=MonthStart(Today(),-2)", "<=MonthEnd(Today(),-2)"    
    This Year, ">=YearStart(Today())"
    Last Year, ">=YearStart(Today(),-1)", "<=YearEnd(Today(),-1)"
];

tab1:
LOAD Date(Today()-IterNo()+1) As tranDate
AutoGenerate 1
While IterNo() <=135
;
Left Join(tab1)
LOAD *
Resident CalRules;

tab2:
LOAD *, Evaluate(Chr(39)&tranDate&Chr(39)&Rule1&If(Not IsNull(Rule2),' And '&Chr(39)&tranDate&Chr(39)&Rule2)) As Eval, 
	Chr(39)&tranDate&Chr(39)&Rule1&If(Not IsNull(Rule2),' And '&Chr(39)&tranDate&Chr(39)&Rule2) As Str
Resident tab1;

tab3:
NoConcatenate
LOAD *
Resident tab2
Where Eval;

Drop Table CalRules, tab1, tab2;
qlikwiz123
Creator II
Creator II
Author

@Saravanan_Desingh 

 

Thank you. But where am I using My own Date Fields (DateA, DateB, DateC and DateD) in these two methods? 

Saravanan_Desingh

I am not sure, how do you want to use it. But its an example creating a Calendar and use it in your Script. Let me show you a simple usage.

CalRules:
LOAD * INLINE [
    Name, Rule1, Rule2
    Today, =Today()
    Yesterday, "=Today()-1"
    Last 7 Days, ">=Today()-7"
    Last 10 Days, ">=Today()-10"
    Last 14 Days, ">=Today()-14"
    Last 30 Days, ">=Today()-30"
    This Week, ">=WeekStart(Today())"
    Last Week, ">=WeekStart(Today(),-1)", "<=WeekEnd(Today(),-1)"
    Last Two Weeks, ">=WeekStart(Today(),-2)", "<=WeekEnd(Today(),-2)"    
    This Month, ">=MonthStart(Today())"
    Last Month, ">=MonthStart(Today(),-1)", "<=MonthEnd(Today(),-1)"
    Last Two Months, ">=MonthStart(Today(),-2)", "<=MonthEnd(Today(),-2)"    
    This Year, ">=YearStart(Today())"
    Last Year, ">=YearStart(Today(),-1)", "<=YearEnd(Today(),-1)"
];

tab1:
LOAD Date(Today()-IterNo()+1) As tranDate
AutoGenerate 1
While IterNo() <=135
;
Left Join(tab1)
LOAD *
Resident CalRules;

tab2:
LOAD *, Evaluate(Chr(39)&tranDate&Chr(39)&Rule1&If(Not IsNull(Rule2),' And '&Chr(39)&tranDate&Chr(39)&Rule2)) As Eval, 
	Chr(39)&tranDate&Chr(39)&Rule1&If(Not IsNull(Rule2),' And '&Chr(39)&tranDate&Chr(39)&Rule2) As Str
Resident tab1;

tabCal:
Mapping 
LOAD tranDate, Name
Resident tab2
Where Eval;

Drop Table CalRules, tab1, tab2;
Map DateA, DateB, DateC, DateD Using tabCal;

Data:
LOAD * INLINE [
    DateA, DateB, DateC, DateD
    1/26/2020, 12/31/2019, 1/1/2020, 4/1/2020
    5/8/2020, 5/10/2020, 5/3/2020, 4/22/2020
];
Saravanan_Desingh

One more usage.

CalRules:
LOAD * INLINE [
    Name, Rule1, Rule2
    Today, =Today()
    Yesterday, "=Today()-1"
    Last 7 Days, ">=Today()-7"
    Last 10 Days, ">=Today()-10"
    Last 14 Days, ">=Today()-14"
    Last 30 Days, ">=Today()-30"
    This Week, ">=WeekStart(Today())"
    Last Week, ">=WeekStart(Today(),-1)", "<=WeekEnd(Today(),-1)"
    Last Two Weeks, ">=WeekStart(Today(),-2)", "<=WeekEnd(Today(),-2)"    
    This Month, ">=MonthStart(Today())"
    Last Month, ">=MonthStart(Today(),-1)", "<=MonthEnd(Today(),-1)"
    Last Two Months, ">=MonthStart(Today(),-2)", "<=MonthEnd(Today(),-2)"    
    This Year, ">=YearStart(Today())"
    Last Year, ">=YearStart(Today(),-1)", "<=YearEnd(Today(),-1)"
];

tab1:
LOAD Date(Today()-IterNo()+1) As tranDate
AutoGenerate 1
While IterNo() <=135
;
Left Join(tab1)
LOAD *
Resident CalRules;

tab2:
LOAD *, Evaluate(Chr(39)&tranDate&Chr(39)&Rule1&If(Not IsNull(Rule2),' And '&Chr(39)&tranDate&Chr(39)&Rule2)) As Eval, 
	Chr(39)&tranDate&Chr(39)&Rule1&If(Not IsNull(Rule2),' And '&Chr(39)&tranDate&Chr(39)&Rule2) As Str
Resident tab1;

tabCal:
Mapping 
LOAD tranDate, Name
Resident tab2
Where Eval;

Drop Table CalRules, tab1, tab2;

Data:
LOAD RowNo() As RowID, *;
LOAD * INLINE [
    DateA, DateB, DateC, DateD
    1/26/2020, 12/31/2019, 1/1/2020, 4/1/2020
    5/8/2020, 5/10/2020, 5/3/2020, 4/22/2020
];

DataX:
CrossTable(Dates, Value)
LOAD RowID, DateA, DateB, DateC, DateD
Resident Data;

DataOut:
LOAD *, ApplyMap('tabCal',Value) As Name
Resident DataX;

Drop Table DataX;
Saravanan_Desingh

If these doesn't helpful, please post a sample App.

qlikwiz123
Creator II
Creator II
Author

Hi,

 

this is just adding all the data fields to the table Data and adding Last 10 Days etc to the values. But does nothing when selected. Also, since 'Data' has more than one date field, it is creating synthetic keys.

 

qlikwiz123_0-1590519703095.png