Date selections on Multiple Date fields

[qlikwiz123]

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. 

[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;

[Saravanan_Desingh]

[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]

@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]

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.