cancel
Showing results for
Did you mean:

Alteryx designer Discussions

SOLVED

Difference in dates and populating exception

Asteroid

HI All,

I have a data set which required to show difference in 2 dates and based on conditions applied on the category field

 category Start date End date Diff in dates Out put A 06/02/2018 06/20/2018 18 A exceeded B 06/02/2018 06/20/2018 18 B exceeded B 06/05/2018 06/20/2018 15 B exceeded A 06/05/2018 06/20/2018 15 A exceeded A 06/04/2018 06/27/2018 23 A exceeded A 06/04/2018 06/27/2018 23 A exceeded A 06/05/2018 06/27/2018 22 A exceeded A 06/05/2018 06/27/2018 22 A exceeded B 06/06/2018 06/27/2018 21 B exceeded B 06/06/2018 06/27/2018 21 B exceeded A 06/07/2018 06/27/2018 20 A exceeded B 06/07/2018 06/27/2018 20 B exceeded A 06/12/2018 06/27/2018 15 A exceeded A 06/12/2018 06/27/2018 15 A exceeded A 06/23/2018 07/09/2018 16 A exceeded B 06/23/2018 07/09/2018 16 B exceeded A 06/23/2018 A exceeded B 06/23/2018 B exceeded

if A>14 days says "A exceeded"

if B>21 days sayes "B Exceeded"

If End date missing "categeory Exceeded(A or B)"

I tried formula for difference in dates i.e DateTimeDiff([start date],[end date],"days") got the difference in dates but for output when i tried this not getting exactly what i am looking

IF Contains([category],"A") &&
[Diff in dates] > "14"
THEN "A exceeded" ELSEIF Contains([category],"B") &&
[Diff in dates] < "21"
THEN "B exceeded" ELSE "failed" ENDIF

Thanks.

Quasar

Here is the sample workflow for your case.  Hope this is helpful. Formula used to get desired result as below. Hope this is helpful.

IIF(Contains([category], "A") and [Difference]>=14, "A Exceeded" , IIF(Contains([category], "B") and [Difference]<=21, "B Exceeded", "Null"))

WorkflowResults

Asteroid

Thanks Ponraj,

every thing working fine, only one condition is missing if end date is blank i.e null then the exception should be like this below.can you please provide a solution for this.

 category Start date End date Diff in dates Out put A 06/23/2018 Null A exceeded B 06/23/2018 Null B exceeded

Highlighted
Meteor

Attached workflow should take care of missing condition.

if [EndDate]=NULL() then [category]+' '+"Exceeded" else [Output] endif

Let us know it works

Quasar

Attached updated workflow.

IIF(Contains([category], "A") and [Difference]>=14, "A Exceeded" , IIF(Contains([category], "B") and [Difference]<=21, "B Exceeded", IIF(IsNull([Difference]) and Contains([category], "A"), "A Exceeded", iif(IsNull([Difference]) and Contains([category], "B"),"B Exceeded","Null"))))

Labels