Need help with set analysis

[m_woolf]

I have a set of data with user ID, Category, CreationDate, and Score fields. A user may have multiple CreationDates. When I average the Scores, I need to average scores for each user for the max CreationDate for that user. I'm stuck on the set analysis to manage this. I have attached an image. Also I have attached a sample qvw and Excel data workbook. Any assistance is appreciated.

[anushree1]

I seem to be missing something here,Can you share how the output must look like; as per the data you have shared if max  date must be considered, i fail to understand why use average we just need to display the score of max date for each user right?

[sunny_talwar · Accepted solution]

May be try this:

Avg(Aggr(FirstSortedValue(Score, -CreationDate), ID, Category))

[m_woolf]

Sunny,

Thanks for your help.  That worked great with the simplified data in my sample, but when I tried to apply it to the more complex actual data it failed. In the actual data there are multiple records with the same ID, Category and Creation data. Evidently the FirstSortedValue function returns null if there are multiple records.

Is there some way to use set analysis something like CreationDate = date(max(CreationDate)) instead.

Thanks again for your time.

[sunny_talwar]

May be you need to use DISTINCT:

Avg(Aggr(FirstSortedValue(DISTINCT Score, -CreationDate), ID, Category))

Or if possible provide a sample which shows the issue you are facing and we might be able to help you out with another expression