How do I use two dates to determine if a project is ahead, behind or on schedule?

[#1]

I have pulled data for all projects where each project corresponds to a row in the table.

Project_name,                Scheduled_date,                Actual_date.

I need an expression that will compare the two dates and returns the bold text below text based on the rules after each.

On Schedule if the Scheduled Date and Actual Date are the same.

Behind Schedule if the Project (Actual Date) has past the Scheduled Date. (no of days by which project is delayed gets displayed on the project list view page)

Ahead of Schedule if the Project (Actual Date) is before the Scheduled Date. (no of days by which project is ahead gets displayed on the project list view page)

[sinanozdemir · Accepted solution]

Hi,

Maybe you can try to write an IF statement:

If(Scheduled_date = Actual_date, 'On Schedule', If(Scheduled_date < Actual_date , 'Behind Schedule', If(Scheduled_date > Actual_date, 'Ahead of Schedule')))

Hope this helps.

[#3]

That worked as expected, but I know realize that in some cases where the actual end date as not been entered yet into the source data, I should probably be using the current date for comparison to determine if the project is late, early or on time.  How do I replace the actual date with today's date? Can Qlik Sense use the sysdate or something like it?

[johnw]

I don't actually recommend this, because it's confusing to read and therefore hard to maintain, but:

pick(sign(Actual_date-Scheduled_date)+2,'Ahead of','On','Behind')&' Schedule'

(Just do what Sinan says.)

[johnw]

Modifying mine:

pick(sign(alt(Actual_date,today())-Scheduled_date)+2,'Ahead of','On','Behind')&' Schedule'

Or probably more clear if less concise:

if(Actual_date > 0

,if(Scheduled_date > Actual_date,'Ahead of Schedule'
,if(Scheduled_date = Actual_date,'On Schedule'

,'Behind Schedule'))

,if(Scheduled_date > today(),'Ahead of Schedule'
,if(Scheduled_date = today(),'On Schedule'

,'Behind Schedule')))