Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
danimarc12
Partner - Creator
Partner - Creator

Cross Selling - Pivot Table

Hello everyone!

I want to display on a pivot table the association between 2 brands in the sales orders. My expected result is something like this:

Immagine WhatsApp 2023-03-20 ore 15.33.31.jpg

I splitted the field "brand" in two fields, "brand" and "brand2" to use both in the pivot table.
I've tried tu use this measure:

count(DISTINCT scontrino) / count(total scontrino)

where scontrino is the sales order but the result is this one:
Cattura.PNG

For sure I'm doing wrong. I have no knlowledge of statistic.

Could anyone help me to solve my issue?

Thanks a lot!

Labels (1)
4 Replies
iliyansomlev
Partner - Creator II
Partner - Creator II

Hello, Danimarc12,

In the way you want to calculate it, you will not get correlation, but rather relative weight (in terms of number of orders) of all brand combinations.

You get the shown result, because your brand and brand2 values are columns in the same table - most probably Sales Order table, and I guess that you have SalesOrderID in this table. If so, instead of loading your brand field again (as brand2) in the same table, try loading it in a new table, that is linked to your Sales Order table via the SalesOrderID keyfield:

NewTable: LOAD SalesOrderID, brand as brand2 resident SalesOrders;

Then you will get in your pivot the relative weights of all combinations.

 

Hope this helps,

Iliyan

danimarc12
Partner - Creator
Partner - Creator
Author

Hello @iliyansomlev , thanks a lot for your answer.

I did as you said, creating a new table:

danimarc12_0-1679328367868.png

where scontrino is the order and marca2 is the brand2 field.

The result is the following:

danimarc12_1-1679328461375.png

It's better than before but I'm afraid if my measure is not correct.

I'm using the same measure:
count(DISTINCT scontrino) / count(TOTAL scontrino)

Also, it should be a value which is not changing by selecting other filters? Because now, if I select any filter, these values % are changing.

Thank a lot 🙏

danimarc12
Partner - Creator
Partner - Creator
Author

danimarc12_2-1679329635269.png

This red one (it's the same brand in the row and column) should be 100%, correct?

iliyansomlev
Partner - Creator II
Partner - Creator II

Hi again,

You get the weight of brands being sold compared to all sales orders. So if you have '-' in some cell this means those two brands were never sold together. The same brand in row and column will not give 100%, because it is also a weight compared to all sales orders - it shows the frequency of this brand being sold.

The weights will be responsive to your selections. If you do not want this to happen, you can use {1} in set analysis or load the data as an island in the model (LOAD scontrino as scontrino2, brand as brand1 Resident Orders and LOAD scontrino as scontrino2, brand as brand2 Resident Orders ) - 2 new brands columns linked via scontrino2 field but unlinked from the rest of your model.

Best regards,

Iliyan