Copying average of two fields to a new field if field is NULL

[Austin]

Good Afternoon Telerik,

 

I currently am averaging to DateTime values from two columns Completed Report and Final Report into one column called CompToFinal

 

I am wanting to average Completed Report to Provisional Report columns. and, sometimes there is No Provisional report but it will ALWAYS have a Final date time. so I want to copy the averaged final time to the provisional column if no provisional value exists or is NULL. I have tried the following code but keep getting errors. Is there a better way to do this?

 

=Avg(Fields.PROVISIONALDATETIME.TimeOfDay - Fields.COMPLETIONDATETIME.TimeOfDay) ?? ? Avg(Fields.FINALDATETIME.TimeOfDay - Fields.COMPLETIONDATETIME.TimeOfDay)

 

Thank you!

[Momchil]

Hi Austin,

I believe that the following expression returns the result you described.

= Avg(Fields.PROVISIONALDATETIME Is Not Null ? Fields.PROVISIONALDATETIME.TimeOfDay - Fields.COMPLETIONDATETIME.TimeOfDay : Fields.FINALDATETIME.TimeOfDay - Fields.COMPLETIONDATETIME.TimeOfDay)

The expression inside the Avg function returns the difference between the provisional and completion time if the provisional time is not null. Otherwise, it returns the difference between the final and completion time.

I am attaching a sample report, for reference.

I hope this helps.

Best Regards,
Momchil
Progress Telerik

[Austin]

Good Morning Momchil,

 

That worked great, Thank you!!! I had one further question.

How can I sort the average times of this argument from Largest to Smallest? Whenever I try to put this into a conditional formatting to make a new field it says it doesn't accept averages. So, I am unsure the best way in Telerik to Sort the average times from largest to smallest time?

 

Thank you!

[Momchil]

Hi Austin,

It is good to hear that my suggestion worked for you.

Regarding your question about sorting, I am not sure I understand how the average time is expected to sort the data. Are you perhaps referring to the calculated time taken for each data record?

Also, how does the conditional formatting relate to the sorting, or did you use it as an example?

That said, here is what I would suggest if we were to sort the data based on the time taken for each item.

1. Add a new sorting rule to the data item used to display the items (in the example from my previous reply, this is the report itself).

2. Set the sorting direction to descending (DESC) and place the expression we used as an argument for the Avg function in the earlier example. That is:

= Fields.PROVISIONALDATETIME Is Not Null ? Fields.PROVISIONALDATETIME.TimeOfDay - Fields.COMPLETIONDATETIME.TimeOfDay : Fields.FINALDATETIME.TimeOfDay - Fields.COMPLETIONDATETIME.TimeOfDay

I am attaching an updated version of my demo report with this change implemented.

In case I misunderstood the requirement, sending a sample report that illustrates it or updating the existing one would be of great help.

[Austin]

Hey Momchil,

 

That worked perfect!!!! Thank you!!!!