Gallery filtered by Datediff in if statement

(0) Share

Report

Posted on by jbrit2020

638

Hello,

I'm trying to filter a gallery based on the datediff within an if statement to establish a threshold of DateDiff numbers such as <=1,<=14, >14

Search(
 Filter('Risk Mitigations',
 Status = Statuses &&
 Department.Value = Viewgr.Value && DateDiff(Today(),'Target Closure Date') <= 14,)
 
 
 ,
 TextSearchBox1_11.Text,"MitigationDescription","Title")

Really stuck on this, any help appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Akser 1,546 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @jbrit2020,

Is there an extra comma there?
Search( Filter('Risk Mitigations', Status = Statuses && Department.Value = Viewgr.Value && DateDiff(Today(),'Target Closure Date') <= 14,) //The comma after 14 must be a typo? , TextSearchBox1_11.Text,"MitigationDescription","Title")

Was this reply helpful? Yes No
jbrit2020 638 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes comma is a typo. Doesn't work regardless of the comma.

Was this reply helpful? Yes No
Akser 1,546 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Not sure if the error is related to your DateDiff formula as it looks OK. The Department check looks good too (I guess choice field in SharePoint?). Can you help me understand the type of Status and Statuses?
Filter('Risk Mitigations', Status = Statuses

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@jbrit2020

You mention an If() function but don't show one in the formula you posted. Is there more to it? Could you post a screenshot showing the full formula and error message.

Was this reply helpful? Yes No
jbrit2020 638 on at

Like
a
(0)

Report
Copy link

Link copied!
It's sorted, I was being an idiot. Do you reckon there's a more elegant way to go about it though?

Search( Filter('Risk Mitigations', Status = Statuses && Department.Value = Viewgr.Value && If(Ragsy = "Green", DateDiff(Today(),'Target Closure Date') > 14,If(Ragsy = "Amber", DateDiff(Today(),'Target Closure Date') >1 &&DateDiff(Today(),'Target Closure Date') <15,DateDiff(Today(),'Target Closure Date') <2))) , TextSearchBox1_11.Text,"MitigationDescription","Title")

Was this reply helpful? Yes No
Akser 1,546 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Maybe a small change can be made to make it easier to read?
If(Ragsy = "Green", DateDiff(Today(),'Target Closure Date') > 14,If(Ragsy = "Amber", DateDiff(Today(),'Target Closure Date') >1 &&DateDiff(Today(),'Target Closure Date') <15,DateDiff(Today(),'Target Closure Date') <2)) //Becomes Switch ( Ragsy, "Green", DateDiff(Today(),'Target Closure Date') > 14, "Amber", DateDiff(Today(),'Target Closure Date') >1 &&DateDiff(Today(),'Target Closure Date') <15, DateDiff(Today(),'Target Closure Date') <2 )

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@jbrit2020

I think this is a bit more elegant:

With({fltrd:Filter('Risk Mitigations',Status = Statuses, Department.Value = Viewgr.Value), dd: DateDiff(Today(),'Target Closure Date') }, Search( Switch(Ragsy, "Green",Filter(fltrd, dd > 14), "Amber", Filter(fltrd, dd > 1), "someothercolor"= Filter(fltrd,dd < 15), //looks like you left one out Filter(fltrd,dd < 2 ), TextSearchBox1_11.Text,"MitigationDescription","Title" ) )

Was this reply helpful? Yes No