FirstSortedValue with conditions

[#1]

A db I work with stores its notes in a compressed format. Decompressing is very CPU intensive, so I have stored all the notes in a .qvd file (after decompressing). The QVD file stores, among other things, the ActivityRep (whoever entered the note in the db), the FullTime the note was entered (down to tenths of a second), and the Note as a text field.

My users want to see the latest note where the note taker was the CSR assigned to the company. I have a straight table with dimensions Company, CSR, forecast$, etc. One of my expressions is

if(HistUser = CSR, FirstSortedValue(FullTime,-FullTime))  <- this works fine to give me the last date the CSR called the client

However, now I want to put the note in the chart as well. I tried:

if(NoteTakerID = CSR, FirstSortedValue(Notes, -FullTime)) <- always shows null, even if there are notes that meet the criteria

When I read the above, I think it reads "If the NoteTakerID is the same as the CSR ID in this row, find the most recent note".

In my tables, CSR and Company are in one table; NoteTakerID, Notes, and FullTime are in a different table. Is there a way to do this using AGGR()?

[sinanozdemir]

Hi Kevin,

Sample data might be very useful in your case. Can you post some sample data if it is not confidential or dummy your data set to something similar to your original data set?

[swuehl]

Try

FirstSortedValue( if(NoteTakerID = CSR,Notes), -FullTime)

[#4]

Hi swuehl,

Tried that already, still gives me a null result.

"Activity_rep" is the same field as "NoteTakerID".

I tried Set Analysis as well: FirstSortedValue({$<[Activity_rep]={CSR}>} Notes, -FullTime), but that didn't work either.

[swuehl]

Since set analysis is evaluated once per chart, not per dimension value, {$<[Activity_rep]={CSR}>}  will not work with CSR being your dimension, unless the chart is limited to a single CSR value.

As Sinan already stated, some sample data lines that show the structure of your tables would really be helpful, it's quite unclear how your data and tables look like in detail.

Since FirstSortedValue() will show NULL when more than one expression values share the same lowest sort order, you can add DISTINCT qualifier to return a value in that case, like

FirstSortedValue(DISTINCT {$<[Activity_rep]={CSR}>} Notes, -FullTime)

But I think key to your issue is to understand how your tables are linked and I seem to haven't understood that really.

Would be best if you could provide a small sample QVW (could contain mock up data values).

[rwunderlich]

Perhaps...

FirstSortedValue({<Notes={"NoteTakerID = CSR"}>}DISTINCT Notes, -FullTime))

-Rob