7 Replies Latest reply: Jun 22, 2017 12:35 PM by anand varadhan

# Pivot Tables, a simple question

Hi All,

I am facing a small problem that I would like to solve

As the following picture, I have a pivot like that:

• Dim 1= Customer
• Dim 2 =Product

The aim of my table is: knowing which customer has bought which product and in which is the first date (my measure), but I am interested also in which product has not bought one customer.

I would like to know if is possible to avoid the fact that when I select one customer for example 'A' the dataset is restricted in automatic to only the product that this customer has bought. My ideal output is that the customer column will be responsive at any customer filter, but the product column not.

I already know that there is the possibility to use 1 in the set analysis or the expression <Customer=>, but by doing that the Customer dimension is not filterable!.

Hoping to be clear,

Regards,

Giulio

• ###### Re: Pivot Tables, a simple question

What is the expression you are using? Would you be able to provide a sample?

• ###### Re: Pivot Tables, a simple question

Hi sunny the expression is just:

• Date(min(Date))

Dimension on the column

• Product (source: fact table and all product table)

Dimension on rows

• Customer (source: fact table and all customer table)

Output expected:

• One pivot table that shows for each product the min (date) for a customer, but I would like to be able to filter on one product and see every customer for this product with the null value for those customers that have not bought the product. What happens is that (due to the fact that there is no link between a Product and a Customer that has not bought it) when I select a product Qlik gives me in automatic just the min(Date) for every Customer that has bought it and ignore in automatic the others. Exists one workaround to avoid this?

I am in a meeting right now so tell me if you need a sample data for this, or if I was not clear enough.

Thanks

• ###### Re: Pivot Tables, a simple question

May be this

Date(min({<Customer = p(Customer), Product>}Date)) + Avg(TOTAL {1} 0)

• ###### Re: Pivot Tables, a simple question

Hi Giulio,

to solve your issue, i suppose, you need an extension

look at this: http://branch.qlik.com/#!/project

i hope that helps

Beck

• ###### Re: Pivot Tables, a simple question

Hi ,

it brings all the customers when a single product is selected plus product is null (when a customer has not bought a product).

Min({<Sheet2.Product,Sheet2.Customer = P({<Sheet2.Product={'\$(=Sheet2.Product)'}>} Sheet2.Customer)>} Sheet2.Date)

• ###### Re: Pivot Tables, a simple question

Hi Anand,

what the command Sheet2 supposed to mean?

I am using Qlik Sense

• ###### Re: Pivot Tables, a simple question

Hi Giulio,

Please ignore Sheet2, the logic is same in qlilksense.

Thanks.

Attached the QS screenshot.