Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Supriya4
Contributor II
Contributor II

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

I have the following table:

Service NameJanRank in JanFebRank in FebMarRank in MarAprRank in AprRanking CounterLatest Month of Ranking
ABC510214467540272457194Apr
BDE289424767137544561214Apr
DGE463734632237306551734Apr
GFH459844591337415553124Apr
.................................
DJE437893554153133173123293Mar
KIJ4234103987283322123481262Mar

 

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?

 

1 Reply
sugathirajkumar
Creator
Creator

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 .