Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Difference in dates and populating exception

shankar12
8 - 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.

4 REPLIES 4
ponraj
13 - 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"))

 

WorkflowWorkflowResultsResults

shankar12
8 - 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

 

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

ponraj
13 - 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