Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Find the most recent instance of a record

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?

5 Replies
MVP
MVP

Find the most recent instance of a record

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

Not applicable

Find the most recent instance of a record

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.

MVP
MVP

Find the most recent instance of a record

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

Not applicable

Find the most recent instance of a record

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?

MVP
MVP

Find the most recent instance of a record

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))

?

Community Browser