Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I was wondering if anyone knows a way, to build a table that would show 3 Rows for every category, based on max amount.
For example, I have 3 category of products, and each category has hundreds of transactions. I need to have a table, with 9 rows, that has the top 10 transactions for each category, order up by Category and then amount.
Here is what I would like to have as a result.
Not that there are plently of ID for each transaction, but I only want to show the 3 with largest amounts.
ID | Category | Amount |
---|---|---|
32 | AA | $4,567 |
123 | AA | $2,345 |
232 | AA | $1,234 |
2345345 | BB | $5,356 |
6563 | BB | $5,123 |
123234 | BB | $4,956 |
6577 | CC | $3,432 |
34432 | CC | $2,456 |
423 | CC | $2,345 |
Any idea is appreciated.
thanks,
Did you try using Straight table dimension limits tab and check show largest 3. ?
Hi,
Maybe you can try with :
=sum({<Id={“=rank (TOTAL <Category> Amount)<=3”}>} Amount)
This will only bring the 3 biggest, no mater the category
I tried this, and might need something different on the formula since this didn't work, but I will dig a bit more into this solution, it seams to go on the right direction. thanks for the answer
=sum({<Id={“=rank (TOTAL <Category> Amount)<=3”}>} Amount)
May be this as Calculated Dimension?
If(Aggr(Rank(Sum(Amount))<=10, Category), Category)