Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Perform calculations based on certain cells in a column.

    Hi,

I'm new to Qlik. I'm trying to create a dashboard for a backpacker hostel. I want to calculate occupancy based on grouping of room types. I have the following data:

Example, I need to find the combined available inventory on each of the dates for

Standard Rooms + Deluxe Rooms + Luxury Rooms + Designer Suites

6 Bed Mixed Dorm + 8 Bed Mixed Dorm

Can anyone help me with this?

      

Hotel CodeRoom TypesTotal InventoryAvailable InventoryDateBlocked Rooms
10566 Bed Mixed Dorm18148/1/20160
10568 Bed Mixed Dorm32248/1/20160
1056Standard Rooms1448/1/20163
1056Deluxe Rooms848/1/20161
1056Luxury Rooms758/1/20160
1056Designer Suites228/1/20160
10566 Bed Female Dorm618/1/20160
10566 Bed Mixed Dorm18138/2/20160
10568 Bed Mixed Dorm32208/2/20160
1056Standard Rooms1418/2/20164
1056Deluxe Rooms848/2/20161
1056Luxury Rooms758/2/20160
1056Designer Suites228/2/20160
10566 Bed Female Dorm638/2/20160
6 Replies
sunny_talwar

Not sure what your expected output needs to look like but can you use Hotel Code and Date as your dimension and Sum([Available Inventory]) as your expression in a table?

Not applicable
Author

Hi Sunny,

I want to group

  • (Standard Rooms + Deluxe Rooms + Luxury Rooms + Designer Suites) as Private rooms
  • (6 Bed Mixed Dorm + 8 Bed Mixed Dorm) as Dorms

Then I want to display total available inventory of Private Rooms and Dorms based on dates.

sunny_talwar

Then you can create a calculated dimension

Dimensions

Hotel Code

Date

If(Match([Room Types], '6 Bed Mixed Dorm', '8 Bed Mixed Dorm'), 'Dorms', 'Private Rooms')

Expression

Sum([Available Inventory])

mkelemen
Creator III
Creator III

Hi Aviral,

you can create something similar using Pivot table.

First you need a extra grouping table:

Grouping:

Load

    *

Inline [

    Room Type, Room Group

    6 Bed Mixed Dorm, Dorms

    8 Bed Mixed Dorm, Dorms

    Standard Rooms, Private rooms

    Deluxe Rooms, Private rooms

    Luxury Rooms, Private rooms

    Designer Suites, Private rooms

]

;

And then create a Pivot table chart with the Room Group as dimension.

You can then enable Show partial sums (in Presentation tab) on the dimension level below the group.

The result looks like this, first 4 columns are dimensions, last 3 are expressions.

rooms.jpg

If you need a full table looking exactly like the one you have, that would be more complicated - using aggr().

BR,

  Matus

Not applicable
Author

Works like a charm. Thanks a lot

Not applicable
Author

Thanks Matus. This works