Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a straight table with these 3 columns:
- Store Name
- Ticket_Number
- Sales
Store Name | Ticket_Number | Sales |
Store 1 | Ticket 1001 | 100 |
Store 1 | Ticket 1002 | 300 |
Store 1 | Ticket 1003 | 400 |
Store 1 | Ticket 1004 | 350 |
Store 2 | Ticket 2001 | 500 |
Store 2 | Ticket 2002 | 800 |
I'd like to get a straight table where for each Store Name I keep only three rows: the top 3 Tickets that store has made sorted from the highest to lowest.
This is what I'd like to get:
Store Name | Ticket_Number | Sales |
Store 1 | Ticket 1003 | 400 |
Store 1 | Ticket 1004 | 350 |
Store 1 | Ticket 1002 | 300 |
Store 2 | Ticket 2002 | 800 |
Store 2 | Ticket 2001 | 500 |
Is it possibile to do that?
Thank you very much for helping.
I've solved this way.
Instead of working on the measure, I've added a new straight table with this new dimension defined by this expression:
=
Aggr(
Aggr(Nodistinct
If(Rank(Sum([Sales]),4,1)<=3, [Ticket_Number])
,[Store Name]
, [Ticket_Number])
,[Ticket_Number])
Now I can add other dimensions and measure in the table and the expression still works.
Hi, perhaps as below.
= If(Aggr(Rank(Sum(Sales)),[Store Name],Ticket_Number)<=3, Sales)
Thank you BrunPierre,
I've come up with this solution and it works. Thank you very much for the input.
= If(Rank(Sum([Sales]),4,1)<=3, Sum([Sales]),)
In the table I have as Dimension Store Code, Store Name, Ticket_Number and this measure.
The problem I currrently have though is that If I add to the table others dimensions for example Date, or SalesPerson_name the expression stops working properly...
As in my suggestion, use the Aggr() function to group these key dimensions.
I've solved this way.
Instead of working on the measure, I've added a new straight table with this new dimension defined by this expression:
=
Aggr(
Aggr(Nodistinct
If(Rank(Sum([Sales]),4,1)<=3, [Ticket_Number])
,[Store Name]
, [Ticket_Number])
,[Ticket_Number])
Now I can add other dimensions and measure in the table and the expression still works.