I'm tracking the number of days between two process events using the calwork

marks over 10 years ago

I'm tracking the number of days between two process events using the calworkdays function, but I''m not getting back any results. The formula checks to see if there was a approval in the process, and then runs the calworkdays function based on that:

=if(pv!pvSupervisorApproval="Approve",(calworkdays(pv!pvSrq_Approved_DateTime,pv!pvSrq_Completed_DateTime)),(calworkdays(pv!pvSrqSubmitted_datetime,pv!pvSrq_Completed_DateTime)))

I've confirmed that all the datestamp fields are "date and time" format, and I can display all the fields above in a report without issue, so I know the data is there. I already have the "Completed" filter in place, so I know the processes are complete. The expression above is set to a "number" format, although I've also tried "Normal text"--either way, I get nothing listed in the result. I've done similar expressions using the if function and the appoval field without issue. If I just run the following:

=calwork...

Discussion posts and replies are publicly visible

0 marks over 10 years ago

...days(pv!pvSrqSubmitted_datetime,pv!pvSrq_Completed_DateTime)

I still get no results, so the issue must be there. Any ideas?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 shelzle over 10 years ago

Could it be that not all process do have these PV values set? It can happen that once the evaluation of a rule for one column failes in only one row, the whole column is blank.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 venkateshr over 10 years ago

One general approach to test out erroneous expressions on reports would be to strip the expression to the basics. In your case, it would be going to the extent of displaying each of the 4 variables you have in your expression in separate columns (the approvalVariable, approvedDateTime, completedDateTime and submittedDateTime).
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 marks over 10 years ago

As far as whether the PV values are always set--that's why I'm using an IF clause. If there was an approval involved, a different set of variables are compared.

As far as stripping out the expressions--I've already displayed the variables involved as separate columns, and they do display correctly.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 marks over 10 years ago

I'm also filtering for completed processes only--if some of the active processes do not have all the values filled (which they don't), would that impact my report?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 shelzle over 10 years ago

From my experience missing PVs or PVs with a "null" value do have impact. Could you try something like this

=if(or(isnull(pv!pvSrqSubmitted_datetime), isnull(pv!pvSrq_Completed_DateTime)), 0, calworkdays(pv!pvSrqSubmitted_datetime,pv!pvSrq_Completed_DateTime))
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 marks over 10 years ago

That did fix the immediate problem--thanks! Now, I will be rolling the results up into an average for all completed tasks. If I use "0" as the default if either of the PVs is null, won't that throw off the everage? If so, I would need to somehow factor out the "0" figures before generating the average, correct?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 shelzle over 10 years ago

If you use "null" instead of "0" these will ignored when the average is calculated.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 marks over 10 years ago

Great--I wasn't sure if I could use a null in a number-formatted field. Thanks again!
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Terri McCormick
Appian Employee
over 10 years ago

Keep in mind, a number(integer) pv defaults to 0, but you can "null it out". For more, see forum.appian.com/.../Data_Types
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel