Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
himatech
New Contributor III

Historical Backlog

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:

backlog-westbury-20170417-01.PNG

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.

Tags (2)
13 Replies
ahaahaaha
Honored Contributor

Re: Historical Backlog

Hi,

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

Regards,

Andrey

himatech
New Contributor III

Re: Historical Backlog

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?

himatech
New Contributor III

Re: Historical Backlog

ahaahaaha could you respond kindly, please?

Thank you in advance..

vinieme12
Esteemed Contributor II

Re: Historical Backlog

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

himatech
New Contributor III

Re: Historical Backlog

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:

backlog-westbury-20170423-01.PNG

vinieme12
Esteemed Contributor II

Re: Historical Backlog

Can you post some sample data to work with?

Preparing examples for Upload - Reduction and Data Scrambling

himatech
New Contributor III

Re: Historical Backlog

Here you go

vinieme12
Esteemed Contributor II

Re: Historical Backlog

What is the expression for [initialBacklog]? 

himatech
New Contributor III

Re: Historical Backlog

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.