Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

parimalpbi
New Contributor

Help me to calculate backlog of tickets still open

I have a data like attached screen shot

TicketData.JPG

Want to have three expressions

1.OpenedTickets,

2.ClosedTickets

3.TickectsStillOpenATtheEndOfTheMonth (Whatever may be the creation date).

So if current selection is Dec-16 then chart should show data from Jan-16. First two expressions are done. how to write third expression.

It will start with Jan-15 with No of tickets still open on 31st -Jan-16  so in above case Ticket_ID = 106 as it is closed after 31-Jan.

So in simple terms no of tickets still open on month end whatever may be creation date.

Backlogged Tickctes Data :

Backlogdata.JPG

5 Replies
Frank_Hartmann
Honored Contributor

Re: Help me to calculate backlog of tickets still open

Hi Parimal,

have a look at the attached sample.

hope this helps

parimalpbi
New Contributor

Re: Help me to calculate backlog of tickets still open

Hi Frank

Thanks for reply. Above solution doesn't seem to work.

In simple terms What I want is no of open tickcts at the end of month regardless of its  open date.

So from above screen shot

103,106 should be counted as backlog in Dec-15

106 should be counted as backlog in Dec-15 and Jan-16.

115 should be counted as backlog in July-16 and Aug-16

Frank_Hartmann
Honored Contributor

Re: Help me to calculate backlog of tickets still open

i think thats exactly what my sample shows.

If you take a deeper look at the "database" picture inside my qvw you can see that there are three "open tickets" in 10/15. One of them was closed in the same month so there are two "open tickets" remaining for 10/15 (see "Total Open"). --> 2  (101 and 102)

In 11/15 you have three "open tickets" plus the two open tickets from 10/15 (=five tickets). And two tickets were closed in 11/15 (one ticket from 10/15 and one ticket from 11/15)..

So three tickets are remaining for 11/15 (see "Total Open") --> 3  (102,103,104)

In 12/15 you have one "open ticket" plus three "open tickets" from 11/15 and two closed Tickets for 12/15, so two "open tickets" are remaining (see "Total Open") --> 2  (103 and 106)

and so on...

Unbenannt.png

So this picture shows all open tickets at monthend disregarding the opening time.

hope this helps

parimalpbi
New Contributor

Re: Help me to calculate backlog of tickets still open

Hi Mate

Thanks for reply.. I was looking for someonother column. your solution works fine but as I have data from 2011 till today .. while loop written is generating hell lot of data casing system to hang. So can not implement it.

Another thing is that upon selection for Dec-14 till Nov-14 it should as well show backlog for Dec-14 which might have opened in somewhere in 2011. This is as well not achivable in above solution.

Can you please suggest other solution might at backend

Highlighted
Frank_Hartmann
Honored Contributor

Re: Help me to calculate backlog of tickets still open

for the performanceproblem i have no solution without knowing your datamodel.

but the dates problem for tickets from 2011 can be solved by editing these lines in script:

Unbenannt.png

hope this helps!