Count Problem

[#1]

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

[swuehl · Accepted solution]

Maybe like attached?

[#3]

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

[swuehl]

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.

[#5]

Thx, very appreciate.

gl