Department and Hotel wise Rank Calculation

[koushik_btech20]

Dear all,

I am facing a problem in calculating rank or position of a Department. I have department ,Client Hotel and competitor hotel wise Department score data. Now suppose for hotel H1 I am creating the analysis , so I have to calculate the  H1 's Department Rank compare to other H2,H3 & H4. I have attached the demo data. PFA and please share if anyone resolve.

Thanks with Regards

Koushik

[koushik_btech20]

Not working showing null...

[tresesco]

Are you looking for an output that only shows a single row - rank of H1 ? Rank is a function that compares the result with the result of the other rows containing the current column segment and returns the ranking of the current row within the segment. So it's important to have all pther relative hotels in display, otherwise, you have to think of alternate means rather than simple rank().

[qlikviewwizard]

Hi,

Can you please attach the required output?

[koushik_btech20]

So is there any alternate way to show the position of a H1's Department in single row.

[tresesco]

Exp:  Aggr( Rank(Sum({<CompetitorsHotelGroup=>}DepartmentScore)),Department,CompetitorsHotelGroup)

Select 'H1', you would get the desired output

[tresesco]

And even if you want it wihout selecting 'H1', try like:

Only({<CompetitorsHotelGroup={H1}>} Aggr( Rank(Sum({<CompetitorsHotelGroup=>}DepartmentScore)),Department,CompetitorsHotelGroup))