Performance issue while loading graphs

Anonymous — Fri, 31 Mar 2017 06:33:11 GMT

I am facing an issue with while loading a certain graph that calculates the outstanding count and the aging of a ticket. I have used the concept of Generating missing dates for calculating the missing dates. As a result the script becomes heavy

This is the script that i have used to calculate the aging and outstanding . So I referred to your blog to optimize the speed. I ran the memory file through the Qlikview optimizer and found that the most consuming memory is the creation of reference open dates. I have pasted the memory distribution pie charts below the script.

Although i have tried to use preceding load, set anaylsis in the expression and flags the graphs are taking a very long time to load. Is there a way to improve the performance of the graphs and is there a way for me to optimize the below script? Can you please suggest what I may be missing out so that the speed of the graphs is improved?

load
[TR #] ,

if(year(ReferencedateOpened)>'2013',Weekend(ReferencedateOpened)) as ReferencedateOpenedweek,

if(year(ReferencedateOpened)>'2013',monthname(ReferencedateOpened)) as ReferencedateOpenedMonth,

if(monthname(ReferencedateOpened)<=AddMonths(today(),-1) and monthname(ReferencedateOpened)>=AddMonths(Today(),-14),monthname(ReferencedateOpened)) as Last13MonthsReferencedateOpened,

if(WeekStart(ReferencedateOpened)<=WeekStart(today()) and WeekStart(ReferencedateOpened)>=WeekStart(Today(),-8),WeekStart(ReferencedateOpened)) as Last8WeeksReferencedateOpened,

if( IsNull([Reference Resolve Date%])=-1 and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened),1,0 ) as vflag_null_Resolve_dates,//Calculating weekly data

if( WeekStart([Reference Resolve Date%]) > WeekStart(ReferencedateOpened) and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened),1,0) as vflag_non_null_Resolve_dates, //Calculating weekly data

if(IsNull([Reference Resolve Date%])=-1 and Monthname([Reference Open Date%])<=Monthname(ReferencedateOpened),1,0) as vflag_null_Resolve_dates_monthly,

if(monthstart([Reference Resolve Date%]) > monthstart(ReferencedateOpened) and monthstart([Reference Open Date%])<=monthstart(ReferencedateOpened),1,0) as vflag_non_null_Resolve_dates_monthly,

if( IsNull([Reference Resolve Date%])=-1 and Monthname([Reference Open Date%])=Monthname(ReferencedateOpened),1,0) as vflag_null_Resolve_dates_StillOpen,

//For 13th month we need to consider tickets that have been resolved in next month as outstanding as well.hence monthstart(Resolved_PR%) > AddMonths(today(),-1) has been considered

if( Monthname([Reference Open Date%])=Monthname(ReferencedateOpened) and monthstart([Reference Resolve Date%]) > AddMonths(today(),-1),1,0) as vflag_non_null_Resolve_dates_StillOpen,

//Calculating monthly aging buckets for Incident Tickets

if( IsNull([Reference Resolve Date%])=-1 and Monthname([Reference Open Date%])<=Monthname(ReferencedateOpened),

if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY'))<=5,'1 week',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY'))<=15,'2-3 weeks',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY'))<=25,'1 month',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY'))<=45,'2 months','>2 months'

))))) as vflag_Aging_null_Resolve_dates_Incident,

if(monthstart([Reference Resolve Date%]) > monthstart(ReferencedateOpened) and monthstart([Reference Open Date%])<=monthstart(ReferencedateOpened),
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY')) <=5,'1 week',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY')) <=15,'2-3 weeks',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY')) <=25,'1 month',
if(networkdays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY')) <=45,'2 months','>2 months'
)))))as vflag_Aging_non_null_Resolve_dates_Incident,

if(WeekStart(ReferencedateOpened)<=WeekStart(today()) and WeekStart(ReferencedateOpened)>=WeekStart(Today(),-8),
if( IsNull([Reference Resolve Date%])=-1 and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened),

if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY'))<=5,'1 week',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY'))<=15,'2-3 weeks',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY'))<=25,'1 month',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY'))<=45,'2 months','>2 months'

)))))) as vflag_Aging_null_Resolve_dates_Incident_weekly,

if(WeekStart(ReferencedateOpened)<=WeekStart(today()) and WeekStart(ReferencedateOpened)>=WeekStart(Today(),-8),
if(WeekStart([Reference Resolve Date%]) > WeekStart(ReferencedateOpened) and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened),
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')) <=5,'1 week',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')) <=15,'2-3 weeks',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')) <=25,'1 month',
if(networkdays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')) <=45,'2 months','>2 months'
))))))as vflag_Aging_non_null_Resolve_dates_Incident_weekly,

if( IsNull([Reference Resolve Date%])=-1 and Monthname([Reference Open Date%])<=Monthname(ReferencedateOpened) , NetWorkDays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY')) )as vflag_Elapsed_Time_null_Resolve_dates_Incidents,

if(monthstart([Reference Resolve Date%]) > monthstart(ReferencedateOpened) and monthstart([Reference Open Date%])<=monthstart(ReferencedateOpened), NetWorkDays([Reference Open Date%],date(monthend(ReferencedateOpened),'MM/DD/YYYY'))) as vflag_Elapsed_Time_non_null_Resolve_dates_Incidents,

if(WeekStart(ReferencedateOpened)<=WeekStart(today()) and WeekStart(ReferencedateOpened)>=WeekStart(Today(),-8),
if( IsNull([Reference Resolve Date%])=-1 and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened) , NetWorkDays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')))) as vflag_Elapsed_Time_null_Resolve_dates_Incidents_weekly,

if(WeekStart(ReferencedateOpened)<=WeekStart(today()) and WeekStart(ReferencedateOpened)>=WeekStart(Today(),-8),
if(WeekStart([Reference Resolve Date%]) > WeekStart(ReferencedateOpened) and WeekStart([Reference Open Date%])<=WeekStart(ReferencedateOpened), NetWorkDays([Reference Open Date%],date(WeekEnd(ReferencedateOpened),'MM/DD/YYYY')))) as vflag_Elapsed_Time_non_null_Resolve_dates_Incidents_weekly,

//Calculating aging buckets for Problem Tickets

if(IsNull([Reference Resolve Date%])=-1 and Monthname([Reference Open Date%])<=Monthname(ReferencedateOpened),
if((today()-[Reference Open Date%])<=90,'0-3 months',
if((today()-[Reference Open Date%])>90 and (today()-[Reference Open Date%])<=180,'3-6 months',
if((today()-[Reference Open Date%])>180 and (today()-[Reference Open Date%])<=270,'6-9 months',
if((today()-[Reference Open Date%])>270 and (today()-[Reference Open Date%])<=360,'9-12 months',
if((today()-[Reference Open Date%])>360,'>12 months'
)))))) as vflag_Aging_null_Resolve_dates_Problems,

if(monthstart([Reference Resolve Date%]) > monthstart(ReferencedateOpened) and monthstart([Reference Open Date%])<=monthstart(ReferencedateOpened),
if((monthend(ReferencedateOpened) - [Reference Open Date%]) <=90,'0-3 months',
if((monthend(ReferencedateOpened) - [Reference Open Date%])>90 and (monthend(ReferencedateOpened) - [Reference Open Date%])<=180,'3-6 months',
if((monthend(ReferencedateOpened) - [Reference Open Date%])>180 and (monthend(ReferencedateOpened) - [Reference Open Date%])<=270,'6-9 months',
if((monthend(ReferencedateOpened) - [Reference Open Date%])>270 and (monthend(ReferencedateOpened) - [Reference Open Date%])<=360,'9-12 months',
if((monthend(ReferencedateOpened) - [Reference Open Date%])>360,'>12 months'
)))))) as vflag_Aging_non_null_Resolve_dates_Problems;

Open_Referencedates:
load
[TR #] ,
date([Reference Open Date%] + IterNo() - 1) as ReferencedateOpened,
[Reference Open Date%],
[Reference Resolve Date%]
Resident Resident_Ticket_Dump

while IterNo() < today() - [Reference Open Date%] +1;

My expression for aging

(sum(aggr(sum( DISTINCT{<[Referred to CPS]={'Y'},RTC_Status={'ReOpened','To be Assigned','Working'}>}vflag_Elapsed_Time_null_Resolve_dates_Incidents_weekly),Id,Last8WeeksReferencedateOpened))+
sum(Aggr(sum ( DISTINCT {<[Referred to CPS]={'Y'},Status={'Open'}>}vflag_Elapsed_Time_null_Resolve_dates_Incidents_weekly),TR,Last8WeeksReferencedateOpened)) +
sum(Aggr(sum ( DISTINCT {<[Referred to CPS]={'Y'},Application={'RRT','CDR','CRS','OKYC'}>}vflag_Elapsed_Time_null_Resolve_dates_Incidents_weekly),[TR #],Last8WeeksReferencedateOpened)) +
sum(Aggr(sum ( DISTINCT {<[Referred to CPS]={'Y'}>}vflag_Elapsed_Time_non_null_Resolve_dates_Incidents_weekly),Id,Last8WeeksReferencedateOpened)) +
sum(Aggr(sum ( DISTINCT {<[Referred to CPS]={'Y'}>}vflag_Elapsed_Time_non_null_Resolve_dates_Incidents_weekly),TR,Last8WeeksReferencedateOpened)) +
sum(Aggr(sum ( DISTINCT {<[Referred to CPS]={'Y'},Application={'RRT','CDR','CRS','OKYC'}>}vflag_Elapsed_Time_non_null_Resolve_dates_Incidents_weekly),[Incident Number],Last8WeeksReferencedateOpened)) )

/

(count(DISTINCT{<vflag_non_null_Resolve_dates={'1'},[Referred to CPS]={'Y'}>}[Incident Number])+
count(DISTINCT{<vflag_non_null_Resolve_dates={'1'},[Referred to CPS]={'Y'}>}Id)+
count(DISTINCT{<vflag_non_null_Resolve_dates={'1'},[Referred to CPS]={'Y'}>}TR)+
count(DISTINCT{<vflag_null_Resolve_dates={'1'},Status={'Open'},[Referred to CPS]={'Y'}>}TR) +
count(DISTINCT{<vflag_null_Resolve_dates={'1'},RTC_Status={'ReOpened','To be Assigned','Working'},[Referred to CPS]={'Y'}>}Id)+
count(DISTINCT{<vflag_null_Resolve_dates={'1'},[Referred to CPS]={'Y'}>}[Incident Number]) )

topic Performance issue while loading graphs in QlikView

Performance issue while loading graphs