Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Help with a pivot table

Henric Cronström,Rob Wunderlich,Miguel Angel Baeyens,

Hi I have a requirement where I need to create a pivot table with a Product field, which has to be used twice, both pivoted and non pivoted position.

The metric that I need to calculate is a venn diagram which will calculate the number of customers which use the combinations of any two products.

following is a sample of the resulting chart i need to create

ProductABC D
A3429
B4863
C26107
D9374

The data is available is Year, Quarter, Product, Country, Customer.

I am facing a problem on how to segregate the values for a pair of particular product types. I tried a lot of ways to create a combination of two products, but I am not able to get the correct numbers.

Please help.

Thanks,

Abhinava

1 Solution

Accepted Solutions
Employee
Employee

Re: Help with a pivot table

A classical Basket analysis...

First: How do you define a "basket"? Is it if a customer has used the two products within a Quarter and in the same Country, or is it worldwide since the beginning of time?

Once you have defined your "basket", you should define an ID for that in the script. Further, you need to create a side table using

ProductB:

Load BasketID, Product as ProductB, ItemID as ItemB_ID Resident Baskets;

Then you will get a data model like

Image3.png

Now you can create your pivot table using Product and ProductB as dimensions, and

     Count(distinct If(ItemID<>ItemB_ID,BasketID))

as expression.

It might be possible to do all this in the pivot table using Set Analysis, Aggr and value loops, but it won't be easy...

HIC

5 Replies
Employee
Employee

Re: Help with a pivot table

A classical Basket analysis...

First: How do you define a "basket"? Is it if a customer has used the two products within a Quarter and in the same Country, or is it worldwide since the beginning of time?

Once you have defined your "basket", you should define an ID for that in the script. Further, you need to create a side table using

ProductB:

Load BasketID, Product as ProductB, ItemID as ItemB_ID Resident Baskets;

Then you will get a data model like

Image3.png

Now you can create your pivot table using Product and ProductB as dimensions, and

     Count(distinct If(ItemID<>ItemB_ID,BasketID))

as expression.

It might be possible to do all this in the pivot table using Set Analysis, Aggr and value loops, but it won't be easy...

HIC

dfoster9
Valued Contributor

Re: Help with a pivot table

Not applicable

Re: Help with a pivot table

Hi HIC,

Thanks for the reply.

The "Basket" that I have to create is the count(customers+country) which use any pair of products and it is for every quarter.

So what I am thinking is to create a table which will give me the Country and Customers which use atleast two products and the combination of those every 2 products.

Am I on the right track?

A little help would be highly appreciated!

Thanks,

Abhinava

Employee
Employee

Re: Help with a pivot table

It sounds to me as if your BasketID should be Quarter + Customer + Country:

     Quarter & '|' & Customer & '|' & Country as BasketID

If you use the solution sketched above, but with this BasketID, I think it will work the way you want it. But you will need to have a selection of a single quarter, otherwise the expression will count the same customer several times - once per quarter.

HIC

Not applicable

Re: Help with a pivot table

Awesome Henric!!

Thanks for all the help!!

Regards,

Abhinava

Community Browser