Dynamic Rank based on two fields

[RichardJH]

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

[patrick_digan · Accepted solution]

@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!

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