How to Filter Dates for a date that is greater than another date?

I am trying to see the count of a column where the date from one field is greater than the date from another date. Here is my example:

Table: Program_Plan_of_care_goal Field: Count (Goal_code) and Goal_actual_completion_date > Goal_target_completion_date

I would assume I could achieve this with a CASE WHEN formula but this does not seem to get what I am looking for.

Rafael Ferreira

Posted 7 months ago·Last reply 6 months ago

4 comments

Rafael Ferreira

OP6 months ago

I actually achieve this by doing a CASE WHEN statement

CASE WHEN MAX(DDIFF(Goal_actual_completion_date, Goal_target_completion_date)) > 1 THEN COUNT(Goal_code) ELSE 0 END.

DRay Seems like there are multiple answers to this quetion

Cause and Effect Strategy

Senior Data Analyst

Sisense Partner

DRay

·7 months ago

Hi Astroraf,

I’m following up to see if the solutions offered by wallingfordce or Ido_QBeeQ worked for you.

If so, please click the 'Accept as Solution' button on the appropriate post. That way others with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

Chris Wallingford

·7 months ago

You can also do this in the front end using DDIFF similarly to the way Ido suggests... flagging rows where the first date is greater than the second date...

IF ( MIN ( DDIFF ( [Days in First Date] , [Days in Second Date] ) ) > 0 , 1 , 0 )

Ido Darnell

·7 months ago · Edited

Hi Astroraf

There are a few ways to achieve this, both would require creating custom columns in the EC table, or creating a new side table to assist with this. You could use the CASE/WHEN but the condition would require int values that represent the dates, or by creating a measured value, that its filter to a flag that applies only to rows which the dates fall into your needed condition.

I would go about this by either of the below options

Create a custom column in the EC table that compares the 2 dates using DAYDIFF(end,start) see this link . If you need to know how many days in between then just use that function, if you only need to know if date 1 is after date 2 then create a CASE/WHEN comparing the 2 dates and just give a 1 or 0 depending on your logic. Then wither use the actual numeric date difference in a CASE/WHEN statement and the condition should check if the new custom columns value is positive or negative. If you end up getting the binary value, just use that in a measured value, and filter to the 1's or 0's accordingly.
Create a custom column in the EC table that converts the dates to an int representation with the formula below Integer (e.g., YYYYMMDD): getyear(Date)*10000 + getmonth(Date)*100 + getday(Date). Then use these int values to compare the dates and see if they end up positive or negative in your formula. Check out this article

LMK if I can further assist in any way,

We're always here to help

Ido from QBeeQ

QBeeQ - Gold Sisense Partner

info@qbeeq.io

Feel free to subscribe to our newsletter to stay up to date with the latest QBeeQ news!