topic Re: Department and Hotel wise Rank Calculation in QlikView

Department and Hotel wise Rank Calculation

koushik_btech20 — Mon, 07 Sep 2015 05:46:28 GMT

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

Re: Department and Hotel wise Rank Calculation

tresB — Mon, 07 Sep 2015 06:15:42 GMT

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

Re: Department and Hotel wise Rank Calculation

MK_QSL — Mon, 07 Sep 2015 06:17:57 GMT

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

Re: Department and Hotel wise Rank Calculation

koushik_btech20 — Mon, 07 Sep 2015 06:23:10 GMT

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

Re: Department and Hotel wise Rank Calculation

koushik_btech20 — Mon, 07 Sep 2015 06:30:10 GMT

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.

Re: Department and Hotel wise Rank Calculation

tresB — Mon, 07 Sep 2015 06:32:42 GMT

Pivot table:

Dim1: Department

Dim2: CompetitorsHoetlGroups

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

Re: Department and Hotel wise Rank Calculation

koushik_btech20 — Mon, 07 Sep 2015 06:40:39 GMT

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

Re: Department and Hotel wise Rank Calculation

MK_QSL — Mon, 07 Sep 2015 06:59:47 GMT

Add second dimension

Re: Department and Hotel wise Rank Calculation

koushik_btech20 — Mon, 07 Sep 2015 07:05:40 GMT

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

Re: Department and Hotel wise Rank Calculation

jyothish8807 — Mon, 07 Sep 2015 07:14:04 GMT

Hi Kaushik,

Use aggr function in expression to group it.

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

in dimension: Department

Regards

Re: Department and Hotel wise Rank Calculation

koushik_btech20 — Mon, 07 Sep 2015 07:18:12 GMT

Not working showing null...

Re: Department and Hotel wise Rank Calculation

tresB — Mon, 07 Sep 2015 07:22:46 GMT

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().

Re: Department and Hotel wise Rank Calculation

qlikviewwizard — Mon, 07 Sep 2015 07:26:00 GMT

Hi,

Can you please attach the required output?

Re: Department and Hotel wise Rank Calculation

koushik_btech20 — Mon, 07 Sep 2015 07:38:24 GMT

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

Re: Department and Hotel wise Rank Calculation

tresB — Mon, 07 Sep 2015 07:56:53 GMT

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

Select 'H1', you would get the desired output

Re: Department and Hotel wise Rank Calculation

tresB — Mon, 07 Sep 2015 08:54:21 GMT

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

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