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

[tresesco]

Like sample attached? You might want to adjust the formula inside Rank() a bit.

[MK_QSL]

NUM(RANK( TOTAL Avg({<TranDate={'>=$(vSDate)<=$(vEDate)'},CompetitorsHotelGroup={'H1'}>}DepartmentScore)/100 , 4))

[koushik_btech20]

Send the expression you used.. I am using Personal Edition, so I cant check your app. 

[koushik_btech20]

The expression you have sent is not working .... Rank will be 1 -4 because there are only 4 hotel in competitor group which I have explained in my attached app.

[tresesco]

Pivot table:

Dim1: Department

Dim2: CompetitorsHoetlGroups

Ex: Rank(Sum(DepartmentScore))              // Now this Sum(... could be adjusted

[koushik_btech20]

Without using Dim 2 means Competitors Hotel Group how you can calculate the rank.

[MK_QSL]

Add second dimension

[koushik_btech20]

Is it possible without adding competitor group because I have show it only Department wise for that selected client hotel.

[jyothish8807]

Hi Kaushik,

Use aggr function in expression to group it.

aggr(Rank(Sum(DepartmentScore)),CompetitorsHoetlGroups)

in dimension: Department

Regards

KC