Calculating Average Backlog Age by Distinct ID

[PBL65]

Hi All,

 I'm new to this form and Qlikview. I have an expression that calculates the distinct count of open requests for past months in a chart as follows:

Count(distinct if(YYYYMM_SUBMITTED<=YYYYMM_BACKLOG and (YYYYMM_COMPLETED>YYYYMM_BACKLOG OR IsNull(YYYYMM_COMPLETED)),R_REQUEST_ID))

The above correctly to returns distinct request count opened prior to the end of the month and closed after. My problem comes in when trying to accurately calculate the age of the backlog at the end of the month. The formula below does not aggregate to R_REQUEST_ID) so requests with multiple rows are not weighted correctly. Any idea how to aggregate to R_REQUEST_ID)? I've tried many times, but the graph will not plot the expression when I add aggr() expressions. Any guidance is appreciated.

Thanks, Paul

avg(if(
YYYYMM_SUBMITTED<=YYYYMM_BACKLOG and (YYYYMM_COMPLETED>YYYYMM_BACKLOG)
,EOP_BACKLOG-SUBMITTED_DATE,
if(
YYYYMM_SUBMITTED<=YYYYMM_BACKLOG and (IsNull(YYYYMM_COMPLETED))
,Today()-SUBMITTED_DATE
)
))

[fosuzuki]

Try this:

avg(aggr(

avg(if(
YYYYMM_SUBMITTED<=YYYYMM_BACKLOG and (YYYYMM_COMPLETED>YYYYMM_BACKLOG)
,EOP_BACKLOG-SUBMITTED_DATE,
if(
YYYYMM_SUBMITTED<=YYYYMM_BACKLOG and (IsNull(YYYYMM_COMPLETED))
,Today()-SUBMITTED_DATE
)
))

, R_REQUEST_ID))

[PBL65]

Hi,

  That plots only one data point. Sorry if this is a tangent, but I wonder if it is because my backlog calendar is floating, so to speak, unlike submission and completion calendars which are joined by a date. Appreciate your help.

Paul

from load script:

//OPENED_CALENDAR:
LEFT JOIN (MAIN) LOAD ProcessDate AS SUBMITTED_DATE,
YYYY AS YYYY_SUBMITTED,
YYYYMM AS YYYYMM_SUBMITTED,
YYYYWK AS YYYYWK_SUBMITTED,
Fiscal_Max_Date_In_Mth AS EOP_SUBMITTED
//Fiscal_Max_Date_In_Qtr
FROM
$(pQVDPath)Calendar.qvd (qvd);

//Calendar independent of open/close dates for backlog
BACKLOG_CALENDAR:
LOAD DISTINCT
YYYY AS YYYY_BACKLOG,
YYYYMM AS YYYYMM_BACKLOG,
YYYYWK AS YYYYWK_BACKLOG,
Fiscal_Max_Date_In_Mth AS EOP_BACKLOG
FROM
$(pQVDPath)Calendar.qvd (qvd)
WHERE Fiscal_Min_Date_In_Mth < Today()
AND YEAR(Today()) - YEAR(Fiscal_Min_Date_In_Mth)<2;

[fosuzuki]

Sorry, this is getting too complicated to understand, so it is hard to help you. Can you share your app and detail what is wrong in which chart?

[PBL65]

See expression AvgAge in bottom-left chart. THANKS!

[Brett_Bleess]

Your backlog calendar is not linked to the rest of your data model, here are some Design Blog posts that should be helpful:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Those two should give you a decent start, here is the main Design Blog link in case you want to search further in this area:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Actually, here are a couple other Help links that may be of some use too:

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/LoadData/best-p...

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/application-per...

Regards,
Brett