Need help with a weird table output format

Hi All,
I have a dataset that needs to be sorted and formatted in a certain way.
In the 'Current Format' table, I have it sorted by the Date and the $$ values
For the final format, I only need the Top 3 Groups based on the $$ value, with the latest year in the far right, and the respective $$ value for each of the top 3 groups. If a specific group doesn't exist, it should default to 0.

ayx_test_data.xlsx

Workflow

Accepted answers

flying008

Hi, @vivaisun11

FYI.

录制_2025_11_06_13_48_26_56.gif

jrlindem

@vivaisun11 Here's another option (as there are many ways to solve your need). Of course @flying008's solution works here too, but I don't like using Multi-Field (or Multi-Row) Formula Tools unless absolutely necessary. So, if you want an alternative option, see below:

That said, I am using the dreaded Cleanse Tool (resource inefficient), but if your workflow does not have a ridiculous amount of data, the Cleanse Tool still has value.

Here's another alternative method. Focusing on changing the Nulls to Zero's:

Hope this helps, -Jay

jrlindem_example.yxmd

All comments

flying008

Hi, @vivaisun11

FYI.

录制_2025_11_06_13_48_26_56.gif

jrlindem

That said, I am using the dreaded Cleanse Tool (resource inefficient), but if your workflow does not have a ridiculous amount of data, the Cleanse Tool still has value.

Here's another alternative method. Focusing on changing the Nulls to Zero's:

Hope this helps, -Jay

jrlindem_example.yxmd

vivaisun11

Hi @flying008

This almost worked perfectly.

However, after the cross-tab tool, the data doesn't remain sorted in descending based on the last column.

Each day, as the data is refreshed, the dates change, but it will always be sorted by the last column.

Any way I can always sort the last column in descending order?

Thanks!

jrlindem

I would create a RowNumb for the dates and then run another aggregation for the RowNumb = Latest_Date = 1 and then use that to sort in the future.

vivaisun11

Hi @jrlindem - I'm not following this part of your solution.

Can you please throw a little more light on this - "create a RowNumb for the dates and then run another aggregation for the RowNumb = Latest_Date = 1 and then use that to sort in the future."

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703