I am trying to rank a dataset by both CompanyRegNo and the Period. I think my workflow configuration using the Multi-Row Formula is not quite right as it goes 1,2,3,4 etc and resets to 1 when the CompanyRegNo changes. I have attached a sample of the output and the rank I am trying to achieve: CustomerNameCompanyRegNoFinanceLineDescPeriodCompany number plus MetricValueDesiredRankE P S SITE SOLUTIONS LIMITEDUK07560406Turnover (£)31/03/2019UK07560406Turnover (£)01E P S SITE SOLUTIONS LIMITEDUK07560406Gross profit (£)31/03/2019UK07560406Gross profit (£)01E P S SITE SOLUTIONS LIMITEDUK07560406Operating profit/(loss) (£)31/03/2019UK07560406Operating profit/(loss) (£)01E P S SITE SOLUTIONS LIMITEDUK07560406Profit/(loss) before tax (£)31/03/2019UK07560406Profit/(loss) before tax (£)01E P S SITE SOLUTIONS LIMITEDUK07560406Net cash at bank/(debt) (£)31/03/2019UK07560406Net cash at bank/(debt) (£)31E P S SITE SOLUTIONS LIMITEDUK07560406Net worth (£)31/03/2019UK07560406Net worth (£)21E P S SITE SOLUTIONS LIMITEDUK07560406Total c. assets less c/ liabilities (£)31/03/2019UK07560406Total c. assets less c/ liabilities (£)221E P S SITE SOLUTIONS LIMITEDUK07560406Current ratio31/03/2019UK07560406Current ratio1.121E P S SITE SOLUTIONS LIMITEDUK07560406Gearing %31/03/2019UK07560406Gearing %981E P S SITE SOLUTIONS LIMITEDUK07560406Creditsafe Score31/03/2019UK07560406Creditsafe Score671E P S SITE SOLUTIONS LIMITEDUK07560406Turnover (£)31/03/2018UK07560406Turnover (£)02E P S SITE SOLUTIONS LIMITEDUK07560406Gross profit (£)31/03/2018UK07560406Gross profit (£)02E P S SITE SOLUTIONS LIMITEDUK07560406Operating profit/(loss) (£)31/03/2018UK07560406Operating profit/(loss) (£)02E P S SITE SOLUTIONS LIMITEDUK07560406Profit/(loss) before tax (£)31/03/2018UK07560406Profit/(loss) before tax (£)02E P S SITE SOLUTIONS LIMITEDUK07560406Net cash at bank/(debt) (£)31/03/2018UK07560406Net cash at bank/(debt) (£)32E P S SITE SOLUTIONS LIMITEDUK07560406Net worth (£)31/03/2018UK07560406Net worth (£)52E P S SITE SOLUTIONS LIMITEDUK07560406Total c. assets less c/ liabilities (£)31/03/2018UK07560406Total c. assets less c/ liabilities (£)122E P S SITE SOLUTIONS LIMITEDUK07560406Current ratio31/03/2018UK07560406Current ratio1.112E P S SITE SOLUTIONS LIMITEDUK07560406Gearing %31/03/2018UK07560406Gearing %6.062E P S SITE SOLUTIONS LIMITEDUK07560406Turnover (£)31/03/2017UK07560406Turnover (£)03E P S SITE SOLUTIONS LIMITEDUK07560406Gross profit (£)31/03/2017UK07560406Gross profit (£)03E P S SITE SOLUTIONS LIMITEDUK07560406Operating profit/(loss) (£)31/03/2017UK07560406Operating profit/(loss) (£)03E P S SITE SOLUTIONS LIMITEDUK07560406Profit/(loss) before tax (£)31/03/2017UK07560406Profit/(loss) before tax (£)03E P S SITE SOLUTIONS LIMITEDUK07560406Net cash at bank/(debt) (£)31/03/2017UK07560406Net cash at bank/(debt) (£)53E P S SITE SOLUTIONS LIMITEDUK07560406Net worth (£)31/03/2017UK07560406Net worth (£)563E P S SITE SOLUTIONS LIMITEDUK07560406Total c. assets less c/ liabilities (£)31/03/2017UK07560406Total c. assets less c/ liabilities (£)3453E P S SITE SOLUTIONS LIMITEDUK07560406Current ratio31/03/2017UK07560406Current ratio1.123E P S SITE SOLUTIONS LIMITEDUK07560406Gearing %31/03/2017UK07560406Gearing %03ADVENTURE LEISURE LIMITEDUK01495746Turnover (£)31/12/2020UK01495746Turnover (£)121ADVENTURE LEISURE LIMITEDUK01495746Gross profit (£)31/12/2020UK01495746Gross profit (£)131ADVENTURE LEISURE LIMITEDUK01495746Operating profit/(loss) (£)31/12/2020UK01495746Operating profit/(loss) (£)141ADVENTURE LEISURE LIMITEDUK01495746Profit/(loss) before tax (£)31/12/2020UK01495746Profit/(loss) before tax (£)151ADVENTURE LEISURE LIMITEDUK01495746Net cash at bank/(debt) (£)31/12/2020UK01495746Net cash at bank/(debt) (£)161ADVENTURE LEISURE LIMITEDUK01495746Net worth (£)31/12/2020UK01495746Net worth (£)211ADVENTURE LEISURE LIMITEDUK01495746Total c. assets less c/ liabilities (£)31/12/2020UK01495746Total c. assets less c/ liabilities (£)221ADVENTURE LEISURE LIMITEDUK01495746Current ratio31/12/2020UK01495746Current ratio0.1031991ADVENTURE LEISURE LIMITEDUK01495746Gearing %31/12/2020UK01495746Gearing %01ADVENTURE LEISURE LIMITEDUK01495746Turnover (£)31/12/2019UK01495746Turnover (£)331ADVENTURE LEISURE LIMITEDUK01495746Gross profit (£)31/12/2019UK01495746Gross profit (£)342ADVENTURE LEISURE LIMITEDUK01495746Operating profit/(loss) (£)31/12/2019UK01495746Operating profit/(loss) (£)3452ADVENTURE LEISURE LIMITEDUK01495746Profit/(loss) before tax (£)31/12/2019UK01495746Profit/(loss) before tax (£)4352ADVENTURE LEISURE LIMITEDUK01495746Net cash at bank/(debt) (£)31/12/2019UK01495746Net cash at bank/(debt) (£)562ADVENTURE LEISURE LIMITEDUK01495746Net worth (£)31/12/2019UK01495746Net worth (£)4652ADVENTURE LEISURE LIMITEDUK01495746Total c. assets less c/ liabilities (£)31/12/2019UK01495746Total c. assets less c/ liabilities (£)52ADVENTURE LEISURE LIMITEDUK01495746Current ratio31/12/2019UK01495746Current ratio0.0944842ADVENTURE LEISURE LIMITEDUK01495746Gearing %31/12/2019UK01495746Gearing %02ADVENTURE LEISURE LIMITEDUK01495746Turnover (£)31/12/2018UK01495746Turnover (£)3452ADVENTURE LEISURE LIMITEDUK01495746Gross profit (£)31/12/2018UK01495746Gross profit (£)7653ADVENTURE LEISURE LIMITEDUK01495746Operating profit/(loss) (£)31/12/2018UK01495746Operating profit/(loss) (£)7653ADVENTURE LEISURE LIMITEDUK01495746Profit/(loss) before tax (£)31/12/2018UK01495746Profit/(loss) before tax (£)6543ADVENTURE LEISURE LIMITEDUK01495746Net cash at bank/(debt) (£)31/12/2018UK01495746Net cash at bank/(debt) (£)6753ADVENTURE LEISURE LIMITEDUK01495746Net worth (£)31/12/2018UK01495746Net worth (£)6753ADVENTURE LEISURE LIMITEDUK01495746Total c. assets less c/ liabilities (£)31/12/2018UK01495746Total c. assets less c/ liabilities (£)5643ADVENTURE LEISURE LIMITEDUK01495746Current ratio31/12/2018UK01495746Current ratio0.0866353ADVENTURE LEISURE LIMITEDUK01495746Gearing %31/12/2018UK01495746Gearing %03ADVENTURE LEISURE LIMITEDUK01495746Creditsafe Score31/12/2020UK01495746Creditsafe Score1001

Dynamic Rank based on two fields

I am trying to rank a dataset by both CompanyRegNo and the Period. I think my workflow configuration using the Multi-Row Formula is not quite right as it goes 1,2,3,4 etc and resets to 1 when the CompanyRegNo changes. I have attached a sample of the output and the rank I am trying to achieve:

CustomerName	CompanyRegNo	FinanceLineDesc	Period	Company number plus Metric	Value	DesiredRank
E P S SITE SOLUTIONS LIMITED	UK07560406	Turnover (£)	31/03/2019	UK07560406Turnover (£)	0	1
E P S SITE SOLUTIONS LIMITED	UK07560406	Gross profit (£)	31/03/2019	UK07560406Gross profit (£)	0	1
E P S SITE SOLUTIONS LIMITED	UK07560406	Operating profit/(loss) (£)	31/03/2019	UK07560406Operating profit/(loss) (£)	0	1
E P S SITE SOLUTIONS LIMITED	UK07560406	Profit/(loss) before tax (£)	31/03/2019	UK07560406Profit/(loss) before tax (£)	0	1
E P S SITE SOLUTIONS LIMITED	UK07560406	Net cash at bank/(debt) (£)	31/03/2019	UK07560406Net cash at bank/(debt) (£)	3	1
E P S SITE SOLUTIONS LIMITED	UK07560406	Net worth (£)	31/03/2019	UK07560406Net worth (£)	2	1
E P S SITE SOLUTIONS LIMITED	UK07560406	Total c. assets less c/ liabilities (£)	31/03/2019	UK07560406Total c. assets less c/ liabilities (£)	22	1
E P S SITE SOLUTIONS LIMITED	UK07560406	Current ratio	31/03/2019	UK07560406Current ratio	1.12	1
E P S SITE SOLUTIONS LIMITED	UK07560406	Gearing %	31/03/2019	UK07560406Gearing %	98	1
E P S SITE SOLUTIONS LIMITED	UK07560406	Creditsafe Score	31/03/2019	UK07560406Creditsafe Score	67	1
E P S SITE SOLUTIONS LIMITED	UK07560406	Turnover (£)	31/03/2018	UK07560406Turnover (£)	0	2
E P S SITE SOLUTIONS LIMITED	UK07560406	Gross profit (£)	31/03/2018	UK07560406Gross profit (£)	0	2
E P S SITE SOLUTIONS LIMITED	UK07560406	Operating profit/(loss) (£)	31/03/2018	UK07560406Operating profit/(loss) (£)	0	2
E P S SITE SOLUTIONS LIMITED	UK07560406	Profit/(loss) before tax (£)	31/03/2018	UK07560406Profit/(loss) before tax (£)	0	2
E P S SITE SOLUTIONS LIMITED	UK07560406	Net cash at bank/(debt) (£)	31/03/2018	UK07560406Net cash at bank/(debt) (£)	3	2
E P S SITE SOLUTIONS LIMITED	UK07560406	Net worth (£)	31/03/2018	UK07560406Net worth (£)	5	2
E P S SITE SOLUTIONS LIMITED	UK07560406	Total c. assets less c/ liabilities (£)	31/03/2018	UK07560406Total c. assets less c/ liabilities (£)	12	2
E P S SITE SOLUTIONS LIMITED	UK07560406	Current ratio	31/03/2018	UK07560406Current ratio	1.11	2
E P S SITE SOLUTIONS LIMITED	UK07560406	Gearing %	31/03/2018	UK07560406Gearing %	6.06	2
E P S SITE SOLUTIONS LIMITED	UK07560406	Turnover (£)	31/03/2017	UK07560406Turnover (£)	0	3
E P S SITE SOLUTIONS LIMITED	UK07560406	Gross profit (£)	31/03/2017	UK07560406Gross profit (£)	0	3
E P S SITE SOLUTIONS LIMITED	UK07560406	Operating profit/(loss) (£)	31/03/2017	UK07560406Operating profit/(loss) (£)	0	3
E P S SITE SOLUTIONS LIMITED	UK07560406	Profit/(loss) before tax (£)	31/03/2017	UK07560406Profit/(loss) before tax (£)	0	3
E P S SITE SOLUTIONS LIMITED	UK07560406	Net cash at bank/(debt) (£)	31/03/2017	UK07560406Net cash at bank/(debt) (£)	5	3
E P S SITE SOLUTIONS LIMITED	UK07560406	Net worth (£)	31/03/2017	UK07560406Net worth (£)	56	3
E P S SITE SOLUTIONS LIMITED	UK07560406	Total c. assets less c/ liabilities (£)	31/03/2017	UK07560406Total c. assets less c/ liabilities (£)	345	3
E P S SITE SOLUTIONS LIMITED	UK07560406	Current ratio	31/03/2017	UK07560406Current ratio	1.12	3
E P S SITE SOLUTIONS LIMITED	UK07560406	Gearing %	31/03/2017	UK07560406Gearing %	0	3
ADVENTURE LEISURE LIMITED	UK01495746	Turnover (£)	31/12/2020	UK01495746Turnover (£)	12	1
ADVENTURE LEISURE LIMITED	UK01495746	Gross profit (£)	31/12/2020	UK01495746Gross profit (£)	13	1
ADVENTURE LEISURE LIMITED	UK01495746	Operating profit/(loss) (£)	31/12/2020	UK01495746Operating profit/(loss) (£)	14	1
ADVENTURE LEISURE LIMITED	UK01495746	Profit/(loss) before tax (£)	31/12/2020	UK01495746Profit/(loss) before tax (£)	15	1
ADVENTURE LEISURE LIMITED	UK01495746	Net cash at bank/(debt) (£)	31/12/2020	UK01495746Net cash at bank/(debt) (£)	16	1
ADVENTURE LEISURE LIMITED	UK01495746	Net worth (£)	31/12/2020	UK01495746Net worth (£)	21	1
ADVENTURE LEISURE LIMITED	UK01495746	Total c. assets less c/ liabilities (£)	31/12/2020	UK01495746Total c. assets less c/ liabilities (£)	22	1
ADVENTURE LEISURE LIMITED	UK01495746	Current ratio	31/12/2020	UK01495746Current ratio	0.103199	1
ADVENTURE LEISURE LIMITED	UK01495746	Gearing %	31/12/2020	UK01495746Gearing %	0	1
ADVENTURE LEISURE LIMITED	UK01495746	Turnover (£)	31/12/2019	UK01495746Turnover (£)	33	1
ADVENTURE LEISURE LIMITED	UK01495746	Gross profit (£)	31/12/2019	UK01495746Gross profit (£)	34	2
ADVENTURE LEISURE LIMITED	UK01495746	Operating profit/(loss) (£)	31/12/2019	UK01495746Operating profit/(loss) (£)	345	2
ADVENTURE LEISURE LIMITED	UK01495746	Profit/(loss) before tax (£)	31/12/2019	UK01495746Profit/(loss) before tax (£)	435	2
ADVENTURE LEISURE LIMITED	UK01495746	Net cash at bank/(debt) (£)	31/12/2019	UK01495746Net cash at bank/(debt) (£)	56	2
ADVENTURE LEISURE LIMITED	UK01495746	Net worth (£)	31/12/2019	UK01495746Net worth (£)	465	2
ADVENTURE LEISURE LIMITED	UK01495746	Total c. assets less c/ liabilities (£)	31/12/2019	UK01495746Total c. assets less c/ liabilities (£)	5	2
ADVENTURE LEISURE LIMITED	UK01495746	Current ratio	31/12/2019	UK01495746Current ratio	0.094484	2
ADVENTURE LEISURE LIMITED	UK01495746	Gearing %	31/12/2019	UK01495746Gearing %	0	2
ADVENTURE LEISURE LIMITED	UK01495746	Turnover (£)	31/12/2018	UK01495746Turnover (£)	345	2
ADVENTURE LEISURE LIMITED	UK01495746	Gross profit (£)	31/12/2018	UK01495746Gross profit (£)	765	3
ADVENTURE LEISURE LIMITED	UK01495746	Operating profit/(loss) (£)	31/12/2018	UK01495746Operating profit/(loss) (£)	765	3
ADVENTURE LEISURE LIMITED	UK01495746	Profit/(loss) before tax (£)	31/12/2018	UK01495746Profit/(loss) before tax (£)	654	3
ADVENTURE LEISURE LIMITED	UK01495746	Net cash at bank/(debt) (£)	31/12/2018	UK01495746Net cash at bank/(debt) (£)	675	3
ADVENTURE LEISURE LIMITED	UK01495746	Net worth (£)	31/12/2018	UK01495746Net worth (£)	675	3
ADVENTURE LEISURE LIMITED	UK01495746	Total c. assets less c/ liabilities (£)	31/12/2018	UK01495746Total c. assets less c/ liabilities (£)	564	3
ADVENTURE LEISURE LIMITED	UK01495746	Current ratio	31/12/2018	UK01495746Current ratio	0.086635	3
ADVENTURE LEISURE LIMITED	UK01495746	Gearing %	31/12/2018	UK01495746Gearing %	0	3
ADVENTURE LEISURE LIMITED	UK01495746	Creditsafe Score	31/12/2020	UK01495746Creditsafe Score	100	1

Workflow

Datasets

Preparation

Accepted answers

patrick_digan

@RichardJH I would sort your data, first by CompanyRegNo, and then by period. Then in your multi-row formula I would first group by CompanyRegNo. Then your formula would be something like: [Row-1:DesiredRank]+If [Row-1:Period]=[Period] then 0 else 1 endif.

By grouping by CompanyRegNo, that will reset it for each Company. The formula takes the prior rank and adds 1 if it's a different date or 0 if it's the same date. That should get your desired rank.

You could also use the tile tool after you've sorted your data, set it to unique value, unique column is Period and group by columns CompanyRegNo. It will output 2 fields, and TileNum should be the same as your desired rank.

Hope that helps!

All comments

patrick_digan

By grouping by CompanyRegNo, that will reset it for each Company. The formula takes the prior rank and adds 1 if it's a different date or 0 if it's the same date. That should get your desired rank.

Hope that helps!

atcodedog05

I generally used to go with formula like below

If [Row-1:Period]=[Period] then [Row-1:DesiredRank] else [Row-1:DesiredRank] +1 endif

But this formula is definitely a different way we can look at it

[Row-1:DesiredRank]+If [Row-1:Period]=[Period] then 0 else 1 endif

Thank you for the knowledge share and exposure @patrick_digan 🙂

RichardJH

Just the formula I needed, thank you @patrick_digan. Sorting the period descending gets the latest period to be a ranking of 1, which I'll use for my work. Thank you once again.