count only not closed tickets

[5abi0001]

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. 🙂

[isorinrusu]

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]

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

[isorinrusu]

Where you have the EndDate.

Regards,

Sorin.

[5abi0001]

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]

May be like this:

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

[5abi0001]

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]

.. does someone can help me?

[timpoismans]

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.