Pivot table show missing values

[Koen_D]

Hi Qlik experts!

I have a pivot table showing sales per product category for every customer. If I filter on a specific customer, it occurs often that this customer hasn't purchased every product category. Qlik then discards this column from view. However, I need to export this table and send it to externals using the same format that includes every column every time. Is there a way to show the columns of all product categories, even if there are no sales in that column when I use a filter?

E.g. 

Sales:
LOAD * INLINE [
SaleID, Customer, ItemID, Amount
1, 'John', 1, 5
2, 'Mike', 2, 10
3, 'Mike', 5, 20
4,'Mike', 6, 30
5,'Ben', 3, 10
6, 'Sam', 2, 5
7,'Sam', 4, 8
8, 'Bob', 1, 10
];

Item:
LOAD * INLINE [
ItemID, Category
1,'A'
2,'A'
3, 'A'
4,'B'
5, 'B'
6,'C'
];

Now when I make a pivot table of Customer, Category and sum(Amount), and I filter on Bob I only see the column with category A. I'd also like to be able to still see categories B and C, so that my export always has the same layout.

I'm looking to do this in Qlik Sense.

[Oleg_Troyansky]

I believe that this blog article should give you an idea:

https://www.naturalsynergies.com/q-tip-20-showing-all-values-in-qlik-sense/

Cheers,

[Koen_D]

Hello Oleg, thanks for this post! It really helped a lot already. I'm quite a bit closer to the solution, however by using this method I can no longer filter my customers. In this situation, if I select customer Bob, I will also see the other customers as well. How can I make it so that I'm still able to filter on the customers out of the table, while keeping in the categories (and add 0 if necessary)?

[vinieme12]

Add dummy data to your sales table for all possible combinations of customer and product

Sales:
LOAD * INLINE [
SaleID, Customer, ItemID, Amount
1, 'John', 1, 5
2, 'Mike', 2, 10
3, 'Mike', 5, 20
4,'Mike', 6, 30
5,'Ben', 3, 10
6, 'Sam', 2, 5
7,'Sam', 4, 8
8, 'Bob', 1, 10
];
Left Join(Sales)

LOAD * INLINE [
ItemID, Category
1,'A'
2,'A'
3, 'A'
4,'B'
5, 'B'
6,'C'
];


Dummydata:
Load Distinct Customer Resident Sales;
Left Join(Dummydata)
Load Distinct Category Resident Sales;

Concatenate(Sales)
Load * Resident Dummydata;
Drop table Dummydata;


exit script;

[Koen_D]

Thanks, this did the trick! 

EDIT: Hi! This worked in the example provided, however for my large dataset of 1M+ customers and 20k+ products is too much. Any other solutions?

[Oleg_Troyansky]

Thank you for your feedback! I never tried it with two dimensions, but I think you could get the desired results by applying my technique only to the Product dimension and not to the Customers dimension, hence allowing the users to select Customers.

Adding all missing combinations as zeroes works, of course, but it might get tough in larger data sets...

Cheers!

[vinieme12 · Accepted solution]

here's another alternative, only add dummy rows for each category    

the idea is to always show a blank row 

Sales:
LOAD *,1 as so_dim INLINE [
SaleID, Customer, ItemID, Amount
1, 'John', 1, 5
2, 'Mike', 2, 10
3, 'Mike', 5, 20
4,'Mike', 6, 30
5,'Ben', 3, 10
6, 'Sam', 2, 5
7,'Sam', 4, 8
8, 'Bob', 1, 10
];

 

Left Join(Sales)
LOAD * INLINE [
ItemID, Category
1,'A'
2,'A'
3, 'A'
4,'B'
5, 'B'
6,'C'
];

Concatenate(Sales)
Load 
FieldValue('Category',recno()) as Category
,null() as SaleID
,'_' as Customer
,null() as ItemID
,0.0000000001 as Amount
,2 as so_dim
AutoGenerate fieldvaluecount('Category');

exit script;

Row:  Customer

Column: Category

MEasure =   sum({$+<so_dim={2},Customer=>}Amount)

Measure Text color:   =pick(only({1}so_dim),black(),white())

on selections

[Koen_D]

Thanks!