get data between two dates

[KNRaju105630]

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!

[Chanty4u]

Try this 

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

[KNRaju105630]

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

[Chanty4u]

Try this 

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

[KNRaju105630]

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

Please read above post

[Chanty4u]

May be this 

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

[KNRaju105630]

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)

[Chanty4u]

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

[KNRaju105630]

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

Same expression try in textbox and please share with me 

[KNRaju105630]

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

@tresesco , could you please have a look