I need to create 2 calculated columns using ranking from multiple columns

Supriya4 — Sat, 16 Nov 2024 20:04:01 GMT

I have the following table:

Service Name	Jan	Rank in Jan	Feb	Rank in Feb	Mar	Rank in Mar	Apr	Rank in Apr	Ranking Counter	Latest Month of Ranking
ABC	5102	1	4467	5	4027	2	4571	9	4	Apr
BDE	2894	2	4767	1	3754	4	5612	1	4	Apr
DGE	4637	3	4632	2	3730	6	5517	3	4	Apr
GFH	4598	4	4591	3	3741	5	5531	2	4	Apr
...	...	...	...	...	...	...	...	...	...	...
DJE	4378	9	3554	15	3133	17	3123	29	3	Mar
KIJ	4234	10	3987	28	3322	12	3481	26	2	Mar

I have used Rank() in order to rank the number of tickets in each month

Ex: since service ABC is having the highest no of tickets in Jan, it has rank 1 (Rank in jan). However the same service is in Rank 5 in Feb.. and so on.

I need a formula to calculate the last 2 columns...

If a particular service name is having a rank(<=20) in all the months, ranking counter must show 4 and the column named "latest month of ranking" would be Apr.

(Since that particular service falls under top 20 in all the months and the latest month being Apr)

Similarly, for service name KIJ, it has been in top 20 for 2 out of 4 months (jan and mar), thus the ranking counter must show 2 and the latest month would be Mar.

This calculation must be made dynamic whenever i obtain the future month's data. i.e.. Next month i would get May data and create a column -- Rank in May.

At this point, the "ranking counter" and "latest month of ranking" must automatically reflect with the new changes.

Is there a feasible solution for this requirement?

Re: I need to create 2 calculated columns using ranking from multiple columns

sugathirajkumar — Wed, 18 Sep 2019 07:43:22 GMT

if(rank(sum(Jan)) <='20',1,0)+
if(rank(sum(Feb)) <='20',1,0)+
if(rank(sum(Mar)) <='20',1,0)+
if(rank(sum(Apr)) <='20',1,0)

This will count the ranks which is less than 20 . Any how you have to manually add the months .

topic Re: I need to create 2 calculated columns using ranking from multiple columns in App Development

I need to create 2 calculated columns using ranking from multiple columns

Re: I need to create 2 calculated columns using ranking from multiple columns