Show top item in ranked table

[Anonymous]

Hi,

I am trying to create a straight table with top 3 users, and their amount. But I also want to include which items was the most that person bought. I attached a qvw with sample data. For this example I wanted to show the following

Name    Item   Amount

Bob       Chips   11

Joe       Candy   7

Mike      Cereal  7

Bobs most bought item was Chips

Joes most bought item was Candy

Mikes most bought item was Cereal

I do not know how to calculate the Item column. For the top three i am using dimension limits to get the top three people with the largest amount.

Thanks in advance!

[tamilarasu]

Hi Pavan,

Try below expression,

Firstsortedvalue(Item ,-Aggr(sum(Amount),Item,Name))

Sample file attached against your data. Let me know. !

[Anonymous]

Thanks Tamil! Could you explain how this works? And also, i am running into an issue when there are multiple items with the same value in first? It shows a dash ( - ) in the box when there are two values that have the same amount. Is there a way to list all of them, hopefully separated by a comma. I was able to get one to show by adding DISTINCT (firstSortedValue NULL) but i would prefer all to show since thats the true ranking. Attached is your qvw with the line

Mike, Books, 1

changed to

Mike, Books, 6

in the script. This shows the issue i am having.

Thanks!

[sunny_talwar · Accepted solution]

Try this expression:

=Concat({<Key = {"=Aggr(NODISTINCT Sum(Amount), Name, Item, Key) = Aggr(Max(TOTAL <Name> Aggr(Sum(Amount), Key, Name, Item)), Name, Item)"}>}DISTINCT Item, ', ')

[Anonymous]

Thanks! This is exactly what i was hoping for. Can you explain how it works?

[sunny_talwar]

Create a straight table with:

Dimensions: Key, Name, Item

Expressions:

1) =Aggr(NODISTINCT Sum(Amount), Name, Item, Key)

2) =Aggr(Max(TOTAL <Name> Aggr(Sum(Amount), Key, Name, Item)), Name, Item)

3) If(Aggr(NODISTINCT Sum(Amount), Name, Item, Key) = Aggr(Max(TOTAL <Name> Aggr(Sum(Amount), Key, Name, Item)), Name, Item), Item)

I created Key, because I needed a unique key for Name and Item where I can test my if condition in set analysis:

So within my set analysis, I am picking up all keys where Aggr(NODISTINCT Sum(Amount), Name, Item, Key) = Aggr(Max(TOTAL <Name> Aggr(Sum(Amount), Key, Name, Item)), Name, Item) (Expression 3) and then concatinating them using Concat function.

Does that make sense?

Best,

Sunny

P.S. You had dimension limit to see top three where as above we are seeing all the max sums.

[Anonymous]

Makes sense. Thanks!