topic Slow Chart Refresh in QlikView

Slow Chart Refresh

dhborchardt — Wed, 02 Feb 2011 21:40:10 GMT

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

Slow Chart Refresh

maneshkhottcpl — Thu, 03 Feb 2011 03:55:50 GMT

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

Slow Chart Refresh

dhborchardt — Thu, 03 Feb 2011 15:55:55 GMT

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.

Slow Chart Refresh

dhborchardt — Thu, 03 Feb 2011 16:03:57 GMT

I forgot to attach.