Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Koen_D
Contributor II
Contributor II

Show missing columns from pivot table

Hello everyone,

I have a pivot table that shows the profit from product groups as columns, and all customers as rows. Right now, when I select a customer that has no purchases in product group A, the column with product group A disappears. How can I make it stay visible? I want the layout of the columns to stay the same regardless of filters. I've tried selecting the "Show null values" button, but it doesn't work as I'd hoped.

Labels (3)
3 Replies
Prathamesh_D
Partner - Contributor II
Partner - Contributor II

Hey ,

@Koen_D  Can you please share the screenshot of ur pivot table ..?

 

Thanks

Koen_D
Contributor II
Contributor II
Author

Hello @Prathamesh_D ,

Here is an example pivot table as follows:

Schermafbeelding 2022-01-17 om 10.44.50.png

If now I select Client_B, that has no purchases in SecondGroup, this column will disappear:

Schermafbeelding 2022-01-17 om 10.47.49.png

 

 The underlying example data is as follows:

inv:
LOAD * INLINE [
Inv, Client, Item, Price
1, Client_A, I_A, 10
2, Client_A, I_B, 10
3, Client_A, I_B, 20
4, Client_B, I_A, 30
5, Client_C, I_C, 25
6, Client_D, I_B, 10
7, Client_D, I_B, 5
8, Client_D, I_C, 20
9, Client_E, I_A, 25
10, Client_E, I_B, 40
11, Client_A, I_C, 5
];

itm:
LOAD * INLINE [
Item, ItemGroup
I_A, FirstGroup
I_B, FirstGroup
I_C, SecondGroup
];

I'd want to always have all columns stay visible, so the export is always consistent.

I know I could transpose the data in the load script so that the invoice table includes product groups as column names, but my client has many different product groups, which makes this impractical.

Koen_D
Contributor II
Contributor II
Author

Anyone has any idea on how to accomplish this?