Historical Backlog

[Anonymous]

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.

[ahaahaaha]

Hi,

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

Regards,

Andrey

[Anonymous]

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?

[Anonymous]

ahaahaaha could you respond kindly, please?

Thank you in advance..

[vinieme12]

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

[Anonymous]

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:

[vinieme12]

Can you post some sample data to work with?

Preparing examples for Upload - Reduction and Data Scrambling

[Anonymous]

Here you go

[vinieme12]

What is the expression for [initialBacklog]? 

[Anonymous]

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.