topic Re: Count Problem in QlikView

Count Problem

Sun, 19 May 2013 19:49:57 GMT

Hi ppl, I guess this should be pretty simple but I cant figured it out.

I have a table with tickets and his status. Every tickets has status Open but not all of them are Closed. So I want to do a count with all the ticket in a pie chart showing how many are closed.

	Ticket, Status
	1, Open
	1, Close
	2, Open
	3, Open
	4, Open
	4, Close

I have 4 tickets 2 of them are closed, so I want the chart showing me 2 open and 2 closed.

I do a count distinct

=count(DISTINCT Ticket)

But It shows me 6 tickets in total, 4 open and 2 closed, so what I want is that if a ticket is closed it shouldnt be counted with the status "Open"

How Can I do this ?

I attached the example

Thx

Re: Count Problem

swuehl — Sun, 19 May 2013 19:58:04 GMT

Maybe like attached?

Re: Count Problem

Sun, 19 May 2013 20:05:04 GMT

Yeah, thx... I never would guess the problem was in the dimension, could u explain this code, pls or tell me another work around more "simple" perhaps

=aggr( FirstSortedValue( Status, -RecID), Ticket)

I have no idea what -RecID is

Thx

Re: Count Problem

swuehl — Sun, 19 May 2013 21:00:45 GMT

I've created RecID in the load script, it's just numbering your record, assuming that higher record number describe a more recent status. You can also use a timestamp / date field instead of the RecID, if available.

There are probably several different approaches, script based or front end based (or combining the two).

I've attached another pure front end approach using the idea that closed tickets have two records, and open tickets only one.

Re: Count Problem

Sun, 19 May 2013 22:13:06 GMT

Thx, very appreciate.