Need help with a weird table output format

[vivaisun11]

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.

[flying008 · Accepted solution]

Hi, @vivaisun11 

FYI.

[jrlindem · Accepted solution]

@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

[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."