Ordering pivoted columns by sale counts

[Former Community Member]

Data contains number of items sold by each agent for each day. I need to visualize a timechart (linechart) of top 10 performers only.

Currently it looks something like this:

Time\Agent	A	B
16.09.2024	3	6
17.09.2024	2	0
18.09.2024	1	2
Total	5	8

I need to order columns by Total sales as there are way too many Agents and I only need to visualize top 10 (then I can limit columns).

Due to pivot I am unable to use merge query to limit either. The only other solution I can come up with is to use a derived table sql.

Ideas are welcome.

[marcwo · Accepted solution]

Hello

I am not sure I understand your request 100%, but iiuc, you are trying to visualize 10 agents (top10 by sales) on a timeline.

For that, I would indeed create a derived table that calculates each agents total sales, add a yesno dimension determining if an agent is top 10 or not, join that back to the initial view and then filter the explore down to agents that are in the top 10 only.

Best
Marc

[Former Community Member]

I was trying to create a line chart of sales by agent. Dimensions: date and measure count pivoted by agent. Since there are more than 200 agents some get truncated. I wanted to check if there are non derived table solution to order the agents by sale totals (data section -> totals). Since that seems unlikely I will do a pivot.

[marcwo]

Sounds good.
Thank you!