cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

###### #SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

SOLVED

## Difference in dates and populating exception

Highlighted
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.

Pulsar

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

Meteor

Attached workflow should take care of missing condition.

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

Let us know it works

Pulsar

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