topic Re: Cross Selling in QlikView

Cross Selling

Arnec — Fri, 15 Nov 2024 23:22:18 GMT

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

Re: Cross Selling

edwin — Thu, 07 Jan 2021 19:29:46 GMT

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?

Re: Cross Selling

Arnec — Mon, 11 Jan 2021 10:56:19 GMT

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.

Re: Cross Selling

edwin — Mon, 11 Jan 2021 13:55:20 GMT

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

Re: Cross Selling

edwin — Mon, 11 Jan 2021 14:12:01 GMT

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

Re: Cross Selling

edwin — Mon, 11 Jan 2021 14:14:40 GMT

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

Re: Cross Selling

edwin — Mon, 11 Jan 2021 14:16:44 GMT

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)

Re: Cross Selling

Arnec — Mon, 11 Jan 2021 18:01:30 GMT

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?

Re: Cross Selling

edwin — Tue, 12 Jan 2021 13:54:23 GMT

@Arnec i would stick with the one using the variable

Re: Cross Selling

Arnec — Tue, 12 Jan 2021 14:07:58 GMT

@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.

Cross Selling

edwin — Tue, 12 Jan 2021 15:10:58 GMT

as item code and item name are different fields the expression will not work. you can create 2 expressions one that assumes the selection is on item code and one that is for item name; then you make the expression conditional on which is selected:

for itemcode, use condition getselectedcount(itemcode)=1 for itemname, use condition getselectedcount(itemname)=1

Re: Cross Selling

edwin — Tue, 12 Jan 2021 15:11:52 GMT