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

1 Answer 66 Views
DataSource SQL Report Designer - Web Report Designer (standalone) Report Parameters
Austin
Top achievements
Rank 2
Iron
Iron
Iron
Austin asked on 19 Jul 2023, 07:43 PM

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!

1 Answer, 1 is accepted

Sort by
1
Accepted
Momchil
Telerik team
answered on 24 Jul 2023, 08:50 AM

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

Stay tuned by visiting our roadmap and feedback portal pages, enjoy a smooth take-off with our Getting Started resources, or visit the free self-paced technical training at https://learn.telerik.com/.
Austin
Top achievements
Rank 2
Iron
Iron
Iron
commented on 28 Jul 2023, 04:04 PM

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
Telerik team
commented on 01 Aug 2023, 12:28 PM

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
Top achievements
Rank 2
Iron
Iron
Iron
commented on 11 Aug 2023, 03:58 AM

Hey Momchil,

 

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

Tags
DataSource SQL Report Designer - Web Report Designer (standalone) Report Parameters
Asked by
Austin
Top achievements
Rank 2
Iron
Iron
Iron
Answers by
Momchil
Telerik team
Share this question
or