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)))

coachVE's avatar
coachVE
Frequent Visitor
6 years ago

You could add an additional calculated column to check if one of the dates fulfills your criteria an then filter by the created column.

criteria check= if( check1 || check 2 || check3; "True" ; "False")
replace the checks with your date logic, eg. date<today


You can chain multiple checks with || , which is like an OR. 

jdchipps's avatar
jdchipps
Frequent Visitor
6 years ago

Thanks CoachVE, i tried that but it returned a positive for every line.  I have got a little further in that I have now got 3 calculated fields that use the below (or similar) dependant on which date I am checking.

 

OverdueResolution = COUNTROWS(FILTER('SR_Service_Calculated',NOT(ISBLANK([Expected_Resolution_Expiration_Date_UTC]))&&[Expected_Resolution_Expiration_Date_UTC]<NOW()))
 
I now just need to combine all 3 in to a single card, but not sure how i can combine these?
  • 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)))