topic Re: get data between two dates in App Development

get data between two dates

KNRaju105630 — Fri, 28 Apr 2023 16:59:21 GMT

Hi Community,

Below is the my table, I have to get data before 12/31/2022 (first I have to consider Date1 and Date2 also have to consider when not exist Date1)

COUNT(ID) should be 4

COUNT({<Date1={"<=$(=DATE#('12/31/2022', 'MM/DD/YYYY'))"}>}ID) - this expression I'm getting 3 only

How to compare both dates ?

ID	Date1	Date2
100	1/1/2023	1/1/2023
101	4/1/2023	4/1/2023
102	1/1/2023	1/1/2023
103	1/1/2023	7/1/2022
104	7/1/2022	4/27/2023
105	3/1/2021	3/1/2021
106	7/1/2020	7/1/2020
107	4/27/2023	4/22/2023

Thanks in Advance!

Re: get data between two dates

Chanty4u — Fri, 28 Apr 2023 17:13:39 GMT

Try this

COUNT({<Date1={"<=$(=DATE#('12/31/2022', 'MM/DD/YYYY'))"}, Date2={"<=$(=DATE#('12/31/2022', 'MM/DD/YYYY'))"}>} ID)

Re: get data between two dates

KNRaju105630 — Fri, 28 Apr 2023 17:38:48 GMT

This will give wrong count.

If it is cover in Date1 should not consider in Date2, .

Count should be 4

First three(104,105 and 106) records from Date1 and 103 record from Date2

103	1/1/2023	7/1/2022
104	7/1/2022	4/27/2023
105	3/1/2021	3/1/2021
106	7/1/2020	7/1/2020

Re: get data between two dates

Chanty4u — Fri, 28 Apr 2023 17:40:46 GMT

Try this

COUNT({<Date1={"<=31/12/2022"}, Date2={"<=$(=DATE#('12/31/2022', 'MM/DD/YYYY'))"}>} DISTINCT ID)

Re: get data between two dates

KNRaju105630 — Fri, 28 Apr 2023 17:44:31 GMT

It won't impact, both expressions are the same.

Please read above post

Re: get data between two dates

Chanty4u — Fri, 28 Apr 2023 17:46:41 GMT

May be this

COUNT({<Date1={"<=$(=DATE#('12/31/2022', 'MM/DD/YYYY'))"}>}ID) + COUNT({<Date1={" "},Date2={"<=$(=DATE#('12/31/2022', 'MM/DD/YYYY'))"}>}ID)

Re: get data between two dates

KNRaju105630 — Fri, 28 Apr 2023 17:54:05 GMT

did you get 4 count for this

COUNT({<Date1={"<=$(=DATE#('12/31/2022', 'MM/DD/YYYY'))"}>}ID) + COUNT({<Date1={" "},Date2={"<=$(=DATE#('12/31/2022', 'MM/DD/YYYY'))"}>}ID)

Re: get data between two dates

Chanty4u — Fri, 28 Apr 2023 17:58:09 GMT

Not sure what your doing from your side...if you read the expression and the results wil come as expected but you can try this in script

// Load the data

MyData:

LOAD * INLINE [

ID, Date1, Date2

100, 1/1/2023, 1/1/2023

101, 4/1/2023, 4/1/2023

102, 1/1/2023, 1/1/2023

103, 1/1/2023, 7/1/2022

104, 7/1/2022, 4/27/2023

105, 3/1/2021, 3/1/2021

106, 7/1/2020, 7/1/2020

107, 4/27/2023, 4/22/2023

];

// Convert date fields to Qlik Sense date format

MyData:

LOAD

ID,

Date(Date1, 'MM/DD/YYYY') as Date1,

Date(Date2, 'MM/DD/YYYY') as Date2

RESIDENT MyData;

// Filter the data to show only the desired rows

FilteredData:

LOAD * WHERE (Date1 <= '12/31/2022' OR Date1 = null) AND Date2 <= '12/31/2022' RESIDENT MyData;

// Output the result

Result:

LOAD *

RESIDENT FilteredData;

Results

ID Date1 Date2

103 1/1/2023 7/1/2022

104 7/1/2022 4/27/2023

105 3/1/2021 3/1/2021

106 7/1/2020 7/1/2020

Re: get data between two dates

KNRaju105630 — Fri, 28 Apr 2023 18:01:10 GMT

Data should not be filtered backend - it should be front end expression.

Same expression try in textbox and please share with me

Re: get data between two dates

KNRaju105630 — Fri, 28 Apr 2023 19:12:16 GMT

Already this expression tried, but giving wrong result for me - could you please help me.

Below expression giving 3 only - but it should be 4

COUNT({<Date1={"<=$(=DATE#('12/31/2022', 'MM/DD/YYYY'))"}>}ID) + COUNT({<Date1={" "},Date2={"<=$(=DATE#('12/31/2022', 'MM/DD/YYYY'))"}>}ID)

103	1/1/2023	7/1/2022
104	7/1/2022	4/27/2023
105	3/1/2021	3/1/2021
106	7/1/2020	7/1/2020

@tresB , could you please have a look

Re: get data between two dates

maxgro — Fri, 28 Apr 2023 19:59:21 GMT

maybe

COUNT({

<Date1={"<=$(=DATE#('31/12/2022', 'MM/DD/YYYY'))"}>

<Date2={"<=$(=DATE#('31/12/2022', 'MM/DD/YYYY'))"}>}

ID)

Re: get data between two dates

KNRaju105630 — Fri, 28 Apr 2023 20:08:58 GMT

not giving any value, PFA,

Re: get data between two dates

maxgro — Fri, 28 Apr 2023 20:44:04 GMT