Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sujana1621
Contributor III
Contributor III

selection based on two timestamps

Hi all,

I Created Startdate and EndDate variables using the below definitions.

vStartDate1 as "=max({<StartDate = {"$(=min(StartDate))"}>} StartDate)"

vEndDate1 as "=Min({<EndDate = {"$(=min(EndDate))"}>} EndDate)"

I have selected dates as below but the data is not getting filtered correctly.

 

sujana1621_0-1632322117602.png

here is the expression used for match date in the table.

=if(timestamp(timestamp#([Match Date],'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm')>='$(vStartDate1)' and timestamp(timestamp#([Match Date],'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY hh:mm')<='$(vEndDate1)',[Match Date])

here is the script that I used to generate data from 2017.

let vStartDate=Timestamp(AddMonths(now(),-48),'YYYY-MM-DD hh:mm:ss');
let vEndDate=Timestamp(now(),'YYYY-MM-DD hh:mm:ss');
trace $(vmindate);

 

LET vMinDate = num(Timestamp(Timestamp#(now()))-1468);

 

LET vMaxDate = num(Timestamp(Timestamp#(now())));

 

LET vNumberOfDays = Floor($(vMaxDate)) - Floor($(vMinDate)) + 1;

 

Calendar_Date_1Months:
LOAD

 

Timestamp(Num($(vMaxDate)) - (RecNo())/24/60 ) as Date,
Num(Floor(Date(Num($(vMaxDate)) - RecNo()/24/60 ))) as DateKey
AutoGenerate 24*60* $(vNumberOfDays) ;

StartDate:

 

LOAD Distinct
Timestamp((Date),'DD/MM/YYYY hh:mm') as StartDate
Resident Calendar_Date_1Months
order by DateKey desc;

EndDate:

 

LOAD Distinct
Timestamp((Date),'DD/MM/YYYY hh:mm') as EndDate
Resident Calendar_Date_1Months
order by DateKey desc;

 

MatchDate is in timestamped format. 

 

sujana1621_1-1632322380104.png

 

Could anyone please help me out on this.

Thanks

Sujana

 

@sunny_talwar 

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

If you are already selecting a single Start Date and a single EndDate, why do you need set analysis in your variable?... I say try this

vStartDate1 as "=max(StartDate)"
vEndDate1 as "=Min(EndDate)"

And then may be this

=If([Match Date] >= $(vStartDate1) and [Match Date] <= $(vEndDate1), [Match Date])

or just this

=If([Match Date] >= [Start Date] and [Match Date] <= [End Date], [Match Date])

View solution in original post

2 Replies
sunny_talwar

If you are already selecting a single Start Date and a single EndDate, why do you need set analysis in your variable?... I say try this

vStartDate1 as "=max(StartDate)"
vEndDate1 as "=Min(EndDate)"

And then may be this

=If([Match Date] >= $(vStartDate1) and [Match Date] <= $(vEndDate1), [Match Date])

or just this

=If([Match Date] >= [Start Date] and [Match Date] <= [End Date], [Match Date])
sujana1621
Contributor III
Contributor III
Author

Thanks so much!! did the same.