Pop-up windows for 'mouse-over' cell values in a Pivot Table

    I recently came across a requirement to show a pop-up window with dynamic content when hovering over a Pivot Table cell value.

    For example, a table of Sales Quantity for products with different units across countries (Meters vs. Feet, Gallon vs. Liter etc...)

     

    There is no explicit way to create a pop-up message for a cell in a pivot table that will dynamically show content according to the row + column values. In our case - show a pop message that will outline the proper unit of measure of Product in a Country.

     

    Let’s use the following data set:

     

    Sales:

    Customer, Country, Product, Quantity

    A, US, Pipe, 300

    B, AU, Pipe, 100

    C, AU, Pipe, 100

    A, US, Water, 1

    D, AU, Water, 1

     

    Units of Measure:

    Product, Country, Unit

    Pipe, US, Foot

    Pipe, AU, Meter

    Water, US, Gallon

    Water, AU, Liter

     

    Load the data to QlikView and create a pivot table with:

    2 dimensions: Product and Country (show Country across columns)

    1 Measure:  sum(Quantity)

     

    The pivot table should appear like that:

    1.png

    If we hover with the mouse on a table cell nothing will happen

     

    Now in order to outline the proper unit of measure of each country/Product –

    Change the expression so it would show the Unit in a another line: (use function chr() for that)

    sum(Quantity)&chr(10)&Unit

     

    At first looks like nothing happened:

    1.png

    However if we hover with the mouse on a certain cell – a pop up message will appear with the unit:

    2.png

     

    I like the solution as it is simple, however we must remember that these cells are not necessarily summable as they hold values of different measures.

     

    Enjoy

     

    Yaniv