Average Using Top 10 Ranked Items

[raynac]

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!

[isingh30]

Please share your data or app.

Thank you!

[raynac]

I sure can, thank you!

Here it is!

[isingh30]

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

Thank you!

[sunny_talwar]

See if the attached is what you wanted?

[raynac]

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!

[sunny_talwar · Accepted solution]

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])) +

(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 Collected RmRev]))

[raynac]

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?

[sunny_talwar]

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

Pivot Table Partial Percentage

[raynac]

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.