topic Re: Historical Backlog in QlikView

Historical Backlog

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have this table in Business Objects which is showing the number of Open tickets and closed over the last 13 monthx and the backlog for each month of those 13 months. I'm trying to have the same one in QlikView:

Here are the expressions I created for the Open (new) and Closed. But I can't get my head around how to the backlog:

New:

=Count(
  TOTAL <ProblemCanonicalDateMonthYear>
  {<
  ProblemCanonicalDateMonthYear={
  '>=$(=date(AddMonths(ProblemCanonicalDateMonthYear,-12),'MMM-YYYY'))<=$(=date(ProblemCanonicalDateMonthYear,'MMM-YYYY'))'
  },
  PRDateType={'PROpenDate'}
  >}
  ID
)

Closed:

=Count(
  TOTAL <ProblemCanonicalDateMonthYear>
  {<
  ProblemCanonicalDateMonthYear={
  '>=$(=date(AddMonths(ProblemCanonicalDateMonthYear,-12),'MMM-YYYY'))<=$(=date(ProblemCanonicalDateMonthYear,'MMM-YYYY'))'
  },
  PRDateType={'PRCloseDate'}
  >}
  ID
)

I've read multiple articles and discussions but I failed to apply a logic behind calculating the backlog historically.

Kindly help me get this done.

Re: Historical Backlog

ahaahaaha — Tue, 18 Apr 2017 07:44:33 GMT

Hi,

In attached file BacklogCalculate field calculate according to the fields New and Closed. May be so?

Regards,

Andrey

Re: Historical Backlog

Anonymous — Tue, 18 Apr 2017 08:16:41 GMT

Thank you for your reply.. I still have the personal edition so I can't open your file 😞

could you kindly paste the load script and the expression on the thread, please?

Re: Historical Backlog

Anonymous — Sun, 23 Apr 2017 09:11:31 GMT

ahaahaaha could you respond kindly, please?

Thank you in advance..

Re: Historical Backlog

vinieme12 — Sun, 23 Apr 2017 10:19:02 GMT

Can you explain your logic of calculating Backlogs? you've nowhere mentioned that.

Aslo can you post a sample data that best represents your data set with expected output.

or if you can post the BO expression for backlog, we can recreate the same using qlikview functions

Re: Historical Backlog

Anonymous — Sun, 23 Apr 2017 10:42:59 GMT

I think sharing how I did on BO would answer all your questions. I have to run three queries on the same report:

New Problems: It extracts the monthly count of new problems over the last 13 months based on the field name [Open Time]
Closed Problems: It extracts the monthly count of closed problems over the last 13 months based on the field name [Close Time]
Open in Period: It extracts the [Problem ID] based on the following criteria:
- (Close Time is Null, OR Close Time is within the selected month OR the preceding 13 months of that selected month), AND
- (Open Time is within the selected month OR the preceding 13 months of that selected month)

Here are the variables:

Initial Backlog:

=If(IsNull(Count([Problem ID]));0;Count([Problem ID]))

New: The count of the problems extracted from the query "New Problems"
Closed: The count of the problems extracted from the query "Closed Problems"
Backlog:

=([Initial Backlog]+[New])-[Closed]
+(Previous([New])-Previous([Closed]))
+(Previous(Previous([New]))-Previous(Previous([Closed])))
+(Previous(Previous(Previous([New])))-Previous(Previous(Previous([Closed]))))
+(Previous(Previous(Previous(Previous([New]))))-Previous(Previous(Previous(Previous([Closed])))))
+(Previous(Previous(Previous(Previous(Previous([New])))))-Previous(Previous(Previous(Previous(Previous([Closed]))))))
+(Previous(Previous(Previous(Previous(Previous(Previous([New]))))))-Previous(Previous(Previous(Previous(Previous(Previous([Closed])))))))
+(Previous(Previous(Previous(Previous(Previous(Previous(Previous([New])))))))-Previous(Previous(Previous(Previous(Previous(Previous(Previous([Closed]))))))))
+(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([New]))))))))-Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([Closed])))))))))
+(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([New])))))))))-Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([Closed]))))))))))
+(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([New]))))))))))-Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([Closed])))))))))))
+(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([New])))))))))))-Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([Closed]))))))))))))
+(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([New]))))))))))))-Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([Closed])))))))))))))
+(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([New])))))))))))))-Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous(Previous([Closed]))))))))))))))

Here is the end result that I'm aiming to achieve:

Re: Historical Backlog

vinieme12 — Sun, 23 Apr 2017 10:47:33 GMT

Can you post some sample data to work with?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Historical Backlog

Anonymous — Sun, 23 Apr 2017 11:00:01 GMT

Here you go

Re: Historical Backlog

vinieme12 — Sun, 23 Apr 2017 11:10:13 GMT

What is the expression for [initialBacklog]?

Re: Historical Backlog

Anonymous — Mon, 24 Apr 2017 05:29:54 GMT

You mean in QlikView? I don't know. I only shared the one of BO.

Allow me to re-iterate, it's extracted from the query "Open in Period". For example, the DB got deployed on 2012 and before this date there was no backlog so consider the backlog as 0.

Re: Historical Backlog

Anonymous — Tue, 25 Apr 2017 07:34:31 GMT

vinieme12

Were you able to look into my reply?

Thank you,

Ibrahim

Re: Historical Backlog

vinieme12 — Tue, 25 Apr 2017 08:27:30 GMT

Hi,

Looking into it.

but what is the logic for initial backlog?

for example how did we arrive at 44 Backlog for 2015/06

Re: Historical Backlog

Anonymous — Tue, 25 Apr 2017 11:01:17 GMT

The [Initial Backlog] counts the number of problem IDs extracted from the query "Open in Period". This query is formed of the following fields:

Problem ID
Open Time (Month/Year)

As explained in an earlier post, the query criteria are the following:

(Close Time is Null, OR Close Time is within the selected month OR the preceding 13 months of that selected month), AND
(Open Time is within the selected month OR the preceding 13 months of that selected month)

I'm sorry that I am not making myself clear enough but this is the maximum I can do to explain. The problem is that I'm not very technical and I've got into this field 4 months only. All I'm doing is self learning before taking it to the next step of a business case to the management.

Re: Historical Backlog

Vitali — Thu, 18 Apr 2019 13:25:06 GMT

Did you manage to get anything new on this topic @Anonymous?