0 Replies Latest reply: Sep 23, 2009 8:57 PM by shaunmccarthy RSS

    Top 3 items in one row...

      Hey Everyone

      We have a database that stores when users request articles on our site, and what tags those articles are associated with (with a "strength" of association).

      Relationship Diagram:

      Username >- Article View -< Tag (with a weighting)

      Currently, we have a table that looks like this:

      Username | Topic | count(aritcles with this topic) | sum(Topic Weightings)

      I was wondering if it would be possible to create a "Top 3" summary table, like the following:

      Username | Topic 1 | Topic 2 | Topic 3
      bob | Cars | Environemnt | Chocolate

      I've managed to do something close with with the following:

      Dimensions:
      =if(aggr(rank(Sum(TagWeighting)), Tag)<=3, Tag)

      Expressions:
      sum(TagWeighting)

      However, if I add username as a dimension, all I end up with is the same 3 top (overall) tags for each user, and things aren't grouped into the same row (i.e. I get 3 rows instead of a row with three columns). Basically I want to do this:

      Dimensions:
      Username

      Expressions:
      The Tag, where the sum of the tag weightings for this tag, is 1st overall for this user
      The Tag, where the sum of the tag weightings for this tag, is 2nd overall for this user
      The Tag, where the sum of the tag weightings for this tag, is 3rd overall for this user

      My Latest Attempt:

      Dimensions:
      Username

      Expressions:
      =if(aggr(rank(Sum(TagWeighting)),Tag) = 1, Tag) [1 most popular Topic]
      =if(aggr(rank(Sum(TagWeighting)),Tag) = 2, Tag) [2 most popular Topic]
      =if(aggr(rank(Sum(TagWeighting)),Tag) = 3, Tag) [3 most popular Topic]

      But I obviously can't get the syntax right :/

      I could load it in from SQL, but I prefer this to be dynamic (e.g. can see what a user's preference was in a particular year etc)

      Thanks,
      Shaun