Cross Selling

[Arnec]

Hi there

I'm trying to do cross selling analysis based on an invoices table that has a line per invoice line:

Document Number
ItemCode
Quantity
Unit Price
Turnover

I found a fex examples here but I can't get it to work. Most of the solutions suggest creating a matrix but I don't really want to load 5 years of invoices twice in my dashboard :).

So how can I select an Itemcode from a listbox and then get an output like this:

Item 1 - sold in 5 orders - for a total quantity of 10
Item 2 - sold in 4 orders - for a total quantity of 5
... etc.

So I think it's clear I need to see all itemcodes with a value > 0 besides the one I select.

Thanks in advance

[edwin]

  what is the relationship between the itemcode you selected and the item codes that you want to report on? can you give sample data and expected result when one of the itemcodes is selected?

[Arnec]

Hi Edwin

Thank you for replying. The relation will be the invoice number. In my table there is a row for each line on the invoice stating the number, item number, quantity, turnover, ... etc.

I would like to be able to select itemcode A and then populate list that contains itemcodes B to Z that appear together on any invoice. Preferably I'll be showing a count of those, a sum of the quantity, turnover, margin ... etc.

[edwin]

you dont have invoice number in the table,


Document Number
ItemCode
Quantity
Unit Price
Turnover

can you share your data or a QVW?  it sounds your challenge is how to represent your data in a Qlik data model and providing a sample of your data and how it is structured will allow others to help

[edwin]

assuming invoice and item code are in the same table (or they can be in separate tables but an association/link exists):

load * inline [
Invoice, itemcode, qty
INV1, A, 12
INV1, B, 11
INV2, B, 10
INV2, C, 13
INV3, A, 14
INV3, F, 15
]

you can create a variable tat stores the selected items code and a straight table with invoice and itemcode as dimension and the expression is 

= sum({<Invoice=p({<itemcode={'$(vItemSelected)'}>}Invoice), itemcode=-{'$(vItemSelected)'}>}qty)

this means get the sum of qty, for item code <> selected code and invoice is the same as invoices with item code = selected item code

[edwin]

in the data example, A is selected, only invoices INV1 and INV3 are valid, only items B and F are also valid

that should address the requirement, get all item codes not = to selected item code but are in the same invoice as selected item code

[edwin]

also if you dont wish to create the variable you can use this expression, assuming the user can only select 1 itemcode:

= sum({<Invoice=p({<itemcode={'$(=only(itemcode))'}>}Invoice), itemcode=-{'$(=only(itemcode))'}>}qty)

[Arnec]

Hi Edwin

Thanks for taking the time to review this. It seems to work! I'm using ItemCode as my dimension and then your formula to calculate the amounts. However both in your example and after transposing the fieldnames to the names in my table the formula gives an error.  I don't know if this is relevant?

[edwin · Accepted solution]

@Arnec  i would stick with the one using the variable

[Arnec]

@edwin thanks. Seems to do the trick. Some difficulty when using other fields to filter such as the Itemname which is in the same line.