Slow Chart Refresh

[dhborchardt]

I have an expression that along with the date dimension will build a chart. Unfortunately the chart takes a long time to refresh if you make a selection. Is there a way to run this expression during reload to create a new field with sum of hours per day ?

The following expression tests AdtDateTime and DscDateTime to determine how many hours fall within each day. Maybe I am way off track and there is an easier way of doing this.

=Sum(IF(AdtDateTime < TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')) AND DscDateTime > TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')+1),24,
IF(AdtDateTime > TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')) AND AdtDateTime < TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')+1) AND DscDateTime >
TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')+1), (TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')+1) - AdtDateTime)*24,
IF(AdtDateTime > TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')) AND DscDateTime < TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')+1), (DscDateTime - AdtDateTime)*24,
IF(DscDateTime > TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')) AND DscDateTime < TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')+1) AND AdtDateTime <
TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')), (DscDateTime - TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')))*24)))))

Thanks

[maneshkhottcpl]

Hi,

U can calculate the expression in backend(load script if fields are in same table else load resident), it will take some extra time while loading the script but ur frontend performance will surly improve.

e.g.

if (AdtDateTime < TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')) AND DscDateTime > TimeStamp(Date(CalendarDate,'MM/DD/YYYY hh:mm:ss')+1) ,1,0) as Flag1,

in this way u can create the flags and use if(Flag=1,24,else part)

will reduce the calculation time in frontend.

If above expression fields are in seperate table in then use leftjoin to join the two tables. as fields from same table reduce the calculation time of the chart.

Good Luck

[dhborchardt]

Thanks

I am not sure I follow. Are you saying I should break the Expression up into Flag1, Flag2, etc? I am unsure on when and how to use the leftjoin.

The expression contains fields from two tables: The PatientVisit table(AdtDateTime and DscDateTime) and the Calendar table(CanlendarDate).

In the attached example I have am using a Resident table for PatientVisit. For patient privacy reasons I cannot offer live data. In production this will be connected to the live OLEDB. An example of how it works with live data is on the 'LivePatDays' script tab.

I understand the need to join the CalendarDate so you can use it in the expression but unsure how to accomplish it.

[dhborchardt]

I forgot to attach.