Generating missing calendar dates

Anonymous — Wed, 11 Jan 2017 08:25:29 GMT

I am using the below script to generate missing calendar dates.

//Generation of missing dates for Problems to track outstanding tickets
Open_Referencedates:
load
[Problem Number] ,
date(Date_Open_date_PR + IterNo() - 1) as Referencedate,
monthname(Date_Open_date_PR + IterNo() - 1,0) as ReferencedateMonth,
Date_Open_date_PR as Opened_PR,
Date_Resolve_date_PR as Resolved_PR

Resident Resident_TR_Dump

while IterNo() < today() - Date_Open_date_PR +1;

load
[Problem Number] ,

if(year(Referencedate)>'2013',Weekend(Referencedate)) as Referencedateweek,

//if(monthstart(Referencedate)>=AddMonths(MonthStart(today()),-13) and monthstart(Referencedate)<AddMonths(MonthStart(today()),0) ,monthname(Referencedate)) as Last13Months_Problems,

if( IsNull(Resolved_PR)=-1 and WeekStart(Opened_PR)<=WeekStart(Referencedate),1,0 ) as vflag_null_Resolve_dates,

if( WeekStart(Resolved_PR) > WeekStart(Referencedate) and WeekStart(Opened_PR)<=WeekStart(Referencedate),1,0) as vflag_non_null_Resolve_dates,

if(IsNull(Resolved_PR)=-1 and Monthname(Opened_PR)<=Monthname(Referencedate),1,0) as vflag_null_Resolve_dates_monthly,

if(MonthStart(Resolved_PR) > MonthStart(Referencedate) and MonthStart(Opened_PR,0)<=MonthStart(Referencedate),1,0) as vflag_non_null_Resolve_dates_monthly

resident Open_Referencedates;

The problem that I am facing is the count is shown incorrectly when I use the expression count(distinct {<vflag_non_null_Resolve_dates_monthly={'1'}>}[Problem Number]) . The count is shown double. But when i click on the on a date in Referencedateweek it shows correct details. I am able to understand why this is happening?

My dimension is ReferencedateMonth and my expression is count(distinct {<vflag_non_null_Resolve_dates_monthly={'1'}>}[Problem Number])
+count(distinct{<vflag_null_Resolve_dates_monthly={'1'}>}[Problem Number] )

I am using a combination of Set analysis and flags as there is performance issue while loading the graphs. User experiences around 5-10 seconds delay whenever the selection is changed

if i dont use flags and use the above directly in the expression tab it is working fine.

topic Generating missing calendar dates in QlikView

Generating missing calendar dates