Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

7 Replies
sunny_talwar

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

beck_bakytbek
Master
Master

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

Anonymous
Not applicable
Author

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

sunny_talwar

May be this

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

kvanandan1
Creator
Creator

Hi ,

Please try below code,

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)

Anonymous
Not applicable
Author

Hi Anand,

what the command Sheet2 supposed to mean?

I am using Qlik Sense

kvanandan1
Creator
Creator

Hi Giulio,

Please ignore Sheet2, the logic is same in qlilksense.

Thanks.

Attached the QS screenshot.

s1.PNG