topic Re: Average Using Top 10 Ranked Items in QlikView

Average Using Top 10 Ranked Items

raynac — Tue, 27 Feb 2018 15:42:56 GMT

I am having an issue where I have been asked to build a chart that shows the top 10 revenue producing guest types in my database, and I have added a column that ranks them. Which was simple.

However, I am now trying to add an Average Daily Rate (which is a calculation of room only revenue over nights). The issue is that, even though I have said not to include non-top-10 items when then chart draws, if I use Expression Total mode on the Expressions tab, that average is not accurate and appears to still based on all records in the selection/database. ADR here should be 195.97; this ADR of 195.11 includes the remaining records not shown.

I can't sum or average rows all the individual ADRs using the Sum (Or Average) of Rows Mode on the Expressions tab:

1) Sum adds them up, which is obviously not what I am after and

2) Regarding Average, it needs to be (Sum(total revenue)/sum(total room nights)) not Avg(ADR)

I have tried Set Analysis in the formula:

=SUM({<[Guest Type]={"=Rank(sum(column(1))<11"}>}column(5)/Column(4))

Column(1) is a somewhat complex calculation of the total revenue that's being used for the ranking, then column(5) is the room revenue and column(4) contains the nights. It's producing all 0s.That was something I saw in a post here somewhere but for me, it's just not working for me.

Can anyone see what I am doing wrong? Or is there a simpler way/setting to get the ADR to do what I need it to?

Thank you for any help you can provide!

Re: Average Using Top 10 Ranked Items

isingh30 — Tue, 27 Feb 2018 16:52:41 GMT

Please share your data or app.

Thank you!

Re: Average Using Top 10 Ranked Items

raynac — Wed, 28 Feb 2018 14:50:30 GMT

I sure can, thank you!

Here it is!

Re: Average Using Top 10 Ranked Items

isingh30 — Wed, 28 Feb 2018 15:16:36 GMT

Why we are using column 1,4 & 5. Don't we have names?

Thank you!

Re: Average Using Top 10 Ranked Items

sunny_talwar — Wed, 28 Feb 2018 15:26:39 GMT

See if the attached is what you wanted?

Re: Average Using Top 10 Ranked Items

raynac — Wed, 28 Feb 2018 15:40:54 GMT

This is exactly what I wanted, thank you!!!

But, what did you do?? I need to be able to replicate it and I can't figure out what you changed!

Re: Average Using Top 10 Ranked Items

sunny_talwar — Wed, 28 Feb 2018 15:51:44 GMT

I changed the expression for Ttl Rm Nghts and Ttl Rm Rev

Ttl Rm Nghts

Sum({<[Guest Type Code] = {"=Rank(sum(if(GetFieldSelections([Group Block Marketing Select])='Exclude' and [Room Night Group Block] = 'Y',0,[Room Night Collected RmRev])) +

(if(wildmatch(GetFieldSelections(_Dimension,',', 10),'*Activity*'), sum(if(GetFieldSelections([Group Block Marketing Select])='Exclude' and [Room Night Group Block] = 'Y',0,[Room Night ActivityRev])),0) +

if(wildmatch(GetFieldSelections(_Dimension,',', 10),'*Other Charge*'), sum(if(GetFieldSelections([Group Block Marketing Select])='Exclude' and [Room Night Group Block] = 'Y',0,[Room Night OtherChargeRev])),0) +

if(wildmatch(GetFieldSelections(_Dimension,',', 10),'*Shuttle*'), sum(if(GetFieldSelections([Group Block Marketing Select])='Exclude' and [Room Night Group Block] = 'Y',0,[Room Night ShuttleRev])),0) +

if(wildmatch(GetFieldSelections(_Dimension,',', 10),'*Spa Services*'), sum(if(GetFieldSelections([Group Block Marketing Select])='Exclude' and [Room Night Group Block] = 'Y',0,[Room Night SpaRev])),0) +

if(wildmatch(GetFieldSelections(_Dimension,',', 10),'*Package*'), sum(if(GetFieldSelections([Group Block Marketing Select])='Exclude' and [Room Night Group Block] = 'Y',0,[Room Night PkgRev])),0) +

if(wildmatch(GetFieldSelections(_Dimension,',', 10),'*Facility*'), sum(if(GetFieldSelections([Group Block Marketing Select])='Exclude' and [Room Night Group Block] = 'Y',0,[Room Night FacilityRev])),0) +

if(wildmatch(GetFieldSelections(_Dimension,',', 10),'*Alternate Inventory*'), sum(if(GetFieldSelections([Group Block Marketing Select])='Exclude' and [Room Night Group Block] = 'Y',0,[Room Night AltInvRev])),0) +

if(wildmatch(GetFieldSelections(_Dimension,',', 10),'*Other Revenue*'), sum(if(GetFieldSelections([Group Block Marketing Select])='Exclude' and [Room Night Group Block] = 'Y',0,[Room Night OtherRev])),0))) < 11"}>}if(GetFieldSelections([Group Block Marketing Select])='Exclude' and [Room Night Group Block] = 'Y',0,[Room Night ReportRmNghts]))

Ttl Rm Rev

sum({<[Guest Type Code] = {"=Rank(sum(if(GetFieldSelections([Group Block Marketing Select])='Exclude' and [Room Night Group Block] = 'Y',0,[Room Night Collected RmRev])) +

Re: Average Using Top 10 Ranked Items

raynac — Wed, 28 Feb 2018 16:09:00 GMT

OH! I was totally focused on the average field; because the numerator and denominator were already correct, it didn't occur to me to rejig those with a set Analysis instead.

Thank you so much. I appreciate your assistance.

I checked out your profile and you seem to be a Qlikview superhero. There is no chance you know how (or if it's even possible) to add a subtotal of the top 10 above the "All Others" line, is there?

Re: Average Using Top 10 Ranked Items

sunny_talwar — Wed, 28 Feb 2018 16:11:58 GMT

You can look at a hack specified in this thread to do it

Pivot Table Partial Percentage

Re: Average Using Top 10 Ranked Items

raynac — Wed, 28 Feb 2018 16:17:15 GMT

WOW. OK...I'll take a look when I have some time to try and implement that. It might take me a little bit of finagling, but thank you so much for pointing me in the right direction.

Re: Average Using Top 10 Ranked Items

Chamini2020 — Mon, 14 Sep 2020 09:37:25 GMT

I m quite new to qlik sense. .

in my data set basically follwoing in fields given

ACcount ID , Client ID, Fiscal date, transaction date , booking date, reservation status, no of room night, no of available rooms , property name, category .. etc.

cant i calcuate, ARR by simply sum(revenue) /Sum(no of room nights)

Thanks