topic Re: Need help with set analysis in QlikView

Need help with set analysis

m_woolf — Tue, 04 Oct 2016 13:51:05 GMT

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.

Re: Need help with set analysis

anushree1 — Tue, 04 Oct 2016 13:59:38 GMT

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?

Re: Need help with set analysis

sunny_talwar — Tue, 04 Oct 2016 13:59:49 GMT

May be try this:

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

Re: Need help with set analysis

m_woolf — Thu, 06 Oct 2016 15:13:54 GMT

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.

Re: Need help with set analysis

sunny_talwar — Thu, 06 Oct 2016 15:17:47 GMT

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