community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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 

 

 

 

categoryStart date End dateDiff in datesOut put
A 06/02/2018 06/20/201818A exceeded
B 06/02/2018 06/20/201818B exceeded
B 06/05/2018 06/20/201815B exceeded
A 06/05/2018 06/20/201815A exceeded
A 06/04/2018 06/27/201823A exceeded
A 06/04/2018 06/27/201823A exceeded
A 06/05/2018 06/27/201822A exceeded
A 06/05/2018 06/27/201822A exceeded
B 06/06/2018 06/27/201821B exceeded
B 06/06/2018 06/27/201821B exceeded
A 06/07/2018 06/27/201820A exceeded
B 06/07/2018 06/27/201820B exceeded
A 06/12/2018 06/27/201815A exceeded
A 06/12/2018 06/27/201815A exceeded
A 06/23/2018 07/09/201816A exceeded
B 06/23/2018 07/09/201816B 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"))

 

workflow.PNGWorkflowResults.PNGResults

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.

 

categoryStart date End dateDiff in datesOut put
A 06/23/2018 Null A exceeded
B 06/23/2018 Null B exceeded

 

Meteor

HI @shankar12

 

Attached workflow should take care of missing condition.

 

Just added this to formula:

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

 

Let us know it works

Highlighted
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