Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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, ', ')
Hi Pavan,
Try below expression,
Firstsortedvalue(Item ,-Aggr(sum(Amount),Item,Name))
Sample file attached against your data. Let me know. !
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!
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, ', ')
Thanks! This is exactly what i was hoping for. Can you explain how it works?
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.
Makes sense. Thanks!