SAC Calculate date difference based on condition

[#1]

Hi everyone,

I have the following issue.

There is a dataset that I am using for a story and am trying to calculate a date difference depending on a third column. In the dataset, I have the following 3 columns:

Created On: contains date data in the format DD.MM.YYYY

Last Changed On: contains date data in the format DD.MM.YYYY

Alert Lifecycle: string data in the categories "Not Started" "In Process" and "Completed".

What I am trying to do is to create a new column Days_Completion and insert the calculated date difference in days between "Created On" and "Last Changed On" for all entries marked as "Completed" in column "Alert Lifecycle", and put a 0 into the rest. As a first step, I was able to calculate it overall without filtering for any rows:

[Days_Completion] =dateDiff([Created On] , [Last Changed On_1] , "Day"

Next, I tried to filter the calculation for only completed alerts with the following formula, but it gives me an error stating that the "=" is unexpected. There are possibly other errors in the calculation too.

[Days_Completion] =if([Alert Lifecycle]="Completed, [dateDiff([Created On] , [Last Changed On_1] , "Day"], "0")

I am very grateful for any kind of input that you could share. Thank you!

Kind regards

[N1kh1l · Accepted solution]

victor_volodin

victor_volodinI assume you are trying this calculation in Data Wrangling. Try the below format.

[Days_Completion] = if([Alert Lifecycle] == "Completed", dateDiff([Created on], [Last Changed On], "Day"), 0)

Sample Output:

Hope this helps !!

Please upvote/accept if this helps you.

Nikhil

[victor_volodin · Accepted solution]

Hi Nikhil,

thank you very much for your quick response. Your answer helped me to resolve my question.

KR,

Victor