topic Re: Calculating Average Backlog Age by Distinct ID in QlikView

Calculating Average Backlog Age by Distinct ID

PBL65 — Fri, 19 Jun 2020 18:28:47 GMT

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
)
))

Re: Calculating Average Backlog Age by Distinct ID

fosuzuki — Sat, 20 Jun 2020 15:57:45 GMT

Try this:

avg(aggr(

, R_REQUEST_ID))

Re: Calculating Average Backlog Age by Distinct ID

PBL65 — Tue, 23 Jun 2020 20:51:39 GMT

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;

Re: Calculating Average Backlog Age by Distinct ID

fosuzuki — Tue, 23 Jun 2020 21:19:43 GMT

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?

Re: Calculating Average Backlog Age by Distinct ID

PBL65 — Wed, 24 Jun 2020 14:29:00 GMT

See expression AvgAge in bottom-left chart. THANKS!

Re: Calculating Average Backlog Age by Distinct ID

Brett_Bleess — Wed, 08 Jul 2020 17:45:58 GMT

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-practices-data-modeling.htm

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

Regards,
Brett