Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ColdSpark
Contributor
Contributor

Count where either of two fields for a given record contain a date within a set time period

I've got a set of data with two date columns, called promo and changepos. I'd like to have an expression in my sheet that checks those two columns, and if EITHER of the dates for a given record is within a set range of dates counts that as "1" towards a count of a total number of rows where either column falls within the range specified.

I've tried using ChatGPT to come up with an answer (which is a fun exercise) and I think it's close with the below but it's not quite working right (the total is lower when I included the second column to consider than when I had just one column under consideration, making me think it's running as an AND not an OR).

=COUNT({<DateField1={"$(=Date(Floor(DateField1), 'YYYY-MM-DD'))"}, DateField2={"$(=Date(Floor(DateField2), 'YYYY-MM-DD'))"}>} TOTAL <DateField1, DateField2> DateField1 & DateField2)

Anyone want to beat the AI? 

Labels (2)
2 Replies
mruehl
Partner - Specialist
Partner - Specialist

Maybe try this:

=COUNT(

{
<
DateField1={"$(=Date(Floor(DateField1), 'YYYY-MM-DD'))"}>
+
<DateField2={"$(=Date(Floor(DateField2), 'YYYY-MM-DD'))"}>

} TOTAL <DateField1, DateField2>

 

DateField1 & DateField2)

hic
Former Employee
Former Employee

If you have a row ID, you can use this in your expression. Further, you write "a set range of dates" which sounds like an upper and a lower bound, or?

If so, I would use:

=Count(
{<ID=
P({<DateField1={">$(LowerBound)<$(UpperBound)"}>} ID) +
P({<DateField2={">$(LowerBound)<$(UpperBound)"}>} ID)
>} ID)

Assuming that the two bounds are stored in variables with the right formatting.

Or if the bounds are static:

=Count(
{<ID=
P({<DateField1={">2023-01-01<2023-02-28"}>} ID) +
P({<DateField2={">2023-01-01<2023-02-28"}>} ID)
>} ID)