topic Re: count only not closed tickets in QlikView

count only not closed tickets

5abi0001 — Mon, 13 Aug 2018 10:03:28 GMT

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

Re: count only not closed tickets

isorinrusu — Mon, 13 Aug 2018 10:23:29 GMT

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.

Re: count only not closed tickets

5abi0001 — Mon, 13 Aug 2018 10:59:18 GMT

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

Re: count only not closed tickets

isorinrusu — Mon, 13 Aug 2018 11:07:08 GMT

Where you have the EndDate.

Regards,

Sorin.

Re: count only not closed tickets

5abi0001 — Mon, 13 Aug 2018 11:21:37 GMT

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

Re: count only not closed tickets

BalaBhaskar_Qlik — Mon, 13 Aug 2018 13:58:20 GMT

May be like this:

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

Re: count only not closed tickets

5abi0001 — Mon, 13 Aug 2018 14:07:08 GMT

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

Re: count only not closed tickets

5abi0001 — Wed, 22 Aug 2018 08:50:53 GMT

.. does someone can help me?

Re: count only not closed tickets

timpoismans — Wed, 22 Aug 2018 09:30:24 GMT

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.