Forum Discussion

jdchipps's avatar
jdchipps
Frequent Visitor
6 years ago
Solved

Filtering if date is before today on 3 separate fields

I have 3 fields in TableA which are Expected_resolution_UTC, Expected_plan_UTC and Expected_response_UTC.  I need to filter a visual (Card) from TableB (there is a link) based on whether any of the a...
Need Help
Show and Tell
  • coachVE's avatar
    coachVE
    6 years ago

    In my opinion youre on a too complicated approach. Always keep your goal in mind.

    From my point of view the goal is to have a column, which is your criteria check and only shows true or false. Then you can create a simple card with the count of your ID filtered by criteria check=true.
    If the criteria check from before didnt work you can also try to use multiple IF functions:
    if(date1<today();True;
    if(date2<today();True;
    if(date3<today();True;False)))

jdchipps's avatar
jdchipps
Frequent Visitor
6 years ago

OK, I am now 1 step further on and can combine the count rows, however I only need to count each row once, i.e. if the date is passed in all 3 fileds I only need to count once.  What I have so far is :

 

OverdueSR = IF(ISBLANK(COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Resplan_Expiration_Date_UTC]))&&[Expected_Resplan_Expiration_Date_UTC]<NOW()))+COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Resolution_Expiration_Date_UTC]))&&[Expected_Resolution_Expiration_Date_UTC]<NOW()))+COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Responded_Expiration_Date_UTC]))&&[Expected_Responded_Expiration_Date_UTC]<NOW()))),"0",COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Resplan_Expiration_Date_UTC]))&&[Expected_Resplan_Expiration_Date_UTC]<NOW()))+COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Resolution_Expiration_Date_UTC]))&&[Expected_Resolution_Expiration_Date_UTC]<NOW()))+COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Responded_Expiration_Date_UTC]))&&[Expected_Responded_Expiration_Date_UTC]<NOW())))
coachVE's avatar
coachVE
Frequent Visitor
6 years ago

In my opinion youre on a too complicated approach. Always keep your goal in mind.

From my point of view the goal is to have a column, which is your criteria check and only shows true or false. Then you can create a simple card with the count of your ID filtered by criteria check=true.
If the criteria check from before didnt work you can also try to use multiple IF functions:
if(date1<today();True;
if(date2<today();True;
if(date3<today();True;False)))