Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

5abi0001
Contributor

count only not closed tickets

Hey!

I'm new at QlikView and i need some help! 🙂

I have the following problem .. i have Ticketnumbers and one Ticketnumber can have several Positions. Now i want to count the Positions, where the ticketnumber is not closed (The EndDate of Ticketnumber is null!) ..

wher should i do the calculation? In the script? Or should i do it in the formula? And how i should do this?

With the following set analysis: =count({$<Position_Start={"*"}>} DistinctTicketnumber_Id) i got all Positions, which are open (because in my table, i only load the positions, where the Position_End field is null)

But the problem is, that sometimes the Ticket is already closed, but the position not. In this case, my formula should not count this Ticketnumber_Id.

The Ticketnumbers are in an own table and the Positions are in there own table .. they are linked trough the Ticketnumber_ID.

If you need more information, please tell me. 🙂

8 Replies
Partner
Partner

Re: count only not closed tickets

Hi,

It's almost always better to do it in the Script.

What you can do is create a new field in the Ticketnumbers table with:

Load Ticketnumber_ID,

...

...

if(len(trim(EndDate))>0,1,0) as NotClosed

From...

After that, you could do a sum(NotClosed) or you could do it using Set Analysis too. The first option is faster.

Regards,

Sorin.

5abi0001
Contributor

Re: count only not closed tickets

Hey! Should i do it in the Table where i have the positions to the tickets or in the table wherei  have the tickets?

Partner
Partner

Re: count only not closed tickets

Where you have the EndDate.

Regards,

Sorin.

5abi0001
Contributor

Re: count only not closed tickets

Hm .. i have EndDate in both tables.

The Tables look like:

Table with Tickets:

Ticketnumber_Id

Open_Date

EndDate

Employee


Table with the Positions:

Ticketnumber_Id

Position

Position_Start

Position_End


The Positions can also have an EndDate .. one ticketnumber can have severel positions .. and sometime because of bad quality of data .. in the Table with Tickets the Ticketnumber_Id is already closed, but in the Table with the Positions, the Position_End is null ..


And now i want only count these positions which are open (have no Position_end date) and where the Tickets are not closed (EndDate is null)


I hope now it is a little bit easier to understand. 🙂

balabhaskarqlik
Honored Contributor

Re: count only not closed tickets

May be like this:

count({$<Position_Start={"*"}, TicketStatus = {"Closed"}> + <Position_End = {" "}>} Distinct Ticketnumber_Id)

Highlighted
5abi0001
Contributor

Re: count only not closed tickets

Hey, in my table with the Positions i already have only these Positions where Position_End is null! 🙂

Because of bad quality of data .. sometime it is so that the Ticket is already closed but the position_end ist null -> and in this case it should not count this ticket. 🙂

5abi0001
Contributor

Re: count only not closed tickets

.. does someone can help me?

timpoismans
Valued Contributor

Re: count only not closed tickets

A possible solution would be to include a flag in each table (Ticket and position) that serves as a open/closed flag.

For Ticket-table:

if(len(trim(EndDate))>0,1,0) as TicketStatus (1 is open, 0 is closed)


Same goes for the Position-table:

if(len(trim(Position_End])>0,1,0) as PositionStatus (1 is open, 0 is closed)


If you then take the TicketStatus and the sum of the PositionStatus you can do multiple checks:

  • TicketStatus = 1, Sum(PositionStatus) >= 1  has the result that the ticket is open, with one or multiple open positions
  • TicketStatus = 1, Sum(PositionStatus) < 1 has the result that the ticket is open, but only has closed positions
  • Ticketstatus = 0, Sum(PositionStatus) >= 1 has the result that the ticket is closed, but there is at least one position that is open
  • Ticketstatus = 0, Sum(PositionStatus) < 1 has the result that the ticket is closed and all positions of the ticket are closed

It's not the best solution, but it should work.