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
