Find the most recent instance of a record

[#1]

I am trying to build a chart that will count the number of clients our company has started.  The chart works wonderfully and does what we want it to do.  UNfortunately, we have discovered that sometimes we have more than one instance of the same client starting.  For example, one client came in three times and then started with us on the third time.  Because he began with us, our database shows the client as starting with us each of the three times he came in.  We only want to count the most recent time in this case, so he only coutns as one client.  I have been exploring the distinct function and code that goes along with it.  What we imagine is simply finding the most recent instance using his opportunityid, which is the same in all three cases.  ANy ideas?

[swuehl]

Hi,

so a count(DISTINCT opportunityid) is not working?

I also could think of something like

=count(aggr( max(Instance), opportunityid))

which I think comes close to your last idea.

Regards,

Stefan

[#3]

Let me show you some code:

count({<Acct_start={1}>}Acct_Pre_Screen_Black)

this is the current code counting the clients who start and sorting them by what we call a presreen type, I can't figur out how to fit in the aggr or distinct within this code so it finds the starts by prescreen but only the most recent addition of another field, the opportunity id.

[swuehl]

So Acct_Pre_Screen_Black is a kind of customer classification?

Are you using this as dimension to your chart or anything else?

Do you have something like a unique CustomerID (or is this your opportunityid)?

Maybe a sample just some lines of data here, would be helpful.

Regards,

Stefan

[#5]

yes pre screen is a customer classification.  the customer id is the opportunity id and that is what we want to find only the most recent instance of.  We have a date field to look up most recent dates. 

Our business stipulates we be very discrete with the information we give out as we are a young growing company.  What would be something you would want to see that would not give away a lot of info?  I cant attach the file, would a screen shot of a table be helpful?

[swuehl]

Still not really sure if I understood your requirement correctly. You don't need to post your real data, but maybe your data model would be good to understand (I hope this is not your core business).

It might be also helpful if you could post just some lines of sample records here (structured like a table, with field names and sample data, but the data could of course be fake).

Ideally, you would post a sample .qvw with fake data but demonstrating your setting and problem.

Anyway I start with just guessing:

So you have chart / table with dimension Acct_Pre_Screen_Black, right? So, I assume you don't want to count the classification like in your above snippet, do you?

To retrieve the the count of customers, it seems that

=count({<Acct_start={1}>} Distinct opportunityid)

is not working, right? Do you have an idea why not (here, some more infos about your model would be nice)

Have you tried

=count(aggr(max({<Acct_start={1}>} Date), opportunityid))

?