Skip to main content

Connectivity & Data Prep

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

Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Perform calculations based on certain cells in a column.


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

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

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.


Then you can create a calculated dimension


Hotel Code


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


Sum([Available Inventory])

Creator III
Creator III

Hi Aviral,

you can create something similar using Pivot table.

First you need a extra grouping table:




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.


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



Not applicable

Works like a charm. Thanks a lot

Not applicable

Thanks Matus. This works