topic Re: Customers who bought 2 Products and nothing else in same category in Visualization and Usability

Customers who bought 2 Products and nothing else in same category

luisccmm — Wed, 17 May 2017 16:41:29 GMT

Hi,

I need to get the SUM os Sales from the Customers who bought 2 products and nothing else from the same category. In this case there are several products and they are organized in different categories.

I am able to get the Sum of Sales from the customers who bought 2 products, but I am not able to exclude the ones that also bought other products from the same category

This will the sum sales in

sum( {$<CLIENT CODE = P({1<[PRODUCT CODE]={"901","518"}>} CLIENT CODE) }>} [SALES] )

But, I need to exclude from the formula above the customers who bought different products from 901 and 518 and pertain to Category1. The expected result: SUM of sales (Customers that only bought Product 901 or 518 in Category1 but do include the sales from this customers in other categories.

Any help??

Re: Customers who bought 2 Products and nothing else in same category

felipedl — Wed, 17 May 2017 19:26:00 GMT

Hi Luis,

See the attached file to see if it helps.

Best regards,

Felipe. !

Re: Customers who bought 2 Products and nothing else in same category

luisccmm — Thu, 18 May 2017 08:26:47 GMT

Hi Felip Drechsler,

Thanks for your answer, but I am not sure that this is what I was looking for, I may have explained myself wrong.

In the formula you displayed on the Qlik App (though I am using Qlik Sense)

(count(ClientCode)=2,

sum({< ClientCode=P({<ProductCode={'901'}>}ClientCode)*P({<ProductCode={'518'}>}ClientCode),ProductCode={'901','518'}>} Sales ))

The first condition count(clientCode)=2 it´s not what I need, because one customer could have bought products from different categories, meaning that its own code could have appeared several times more than 2 in the dataset. Also one customer could have bought products 901 and 518 several times, and that will increase the Count of ClientCode also.

I hope this Example helps to understand the problem:

"Client Code" "Category Product Code " "Product Code" Sales

001 2 901 101

001 2 518 51

001 2 600 201

001 3 350 301

002 2 901 102

002 2 518 52

002 2 901 202

002 3 350 302

002 4 313 502

In the example above the expected Result:

Sales from the Client 001, are ALL excluded because this costumer has bought also other Product Code from Category 2, that are different from "Product Code" 901 and 518.

ALL Sales from the Client 002, should ALL be INCLUDED, because this customer has not bought other product codes different from 901 and 518 from category 2. All sales from customer 002 are included, this also included sales from other categories so: 102 +52 +202 +302+502

Re: Customers who bought 2 Products and nothing else in same category

vinieme12 — Thu, 18 May 2017 08:28:58 GMT

check my sample app here, this should give you some insight

Re: Nested Set Analysis - Customer bought in both time periods

Re: Customers who bought 2 Products and nothing else in same category

luisccmm — Thu, 18 May 2017 08:55:56 GMT

Hi Vineeth,

Thanks for your answer, but with the formulas on that App, I am still not being able to produce any difference from what I have at the moment.

The formula more close to what I need to produce is close to what I have at the moment, but not exaclty

sum( {$<CLIENT CODE = P({1<[PRODUCT CODE]={"901","518"}>} CLIENT CODE) }>} [SALES] )

I need to sum the above and EXCLUDE customers who bought 901 or 518 and also other product codes from the Category to witch Product Codes 901 and 518 belong to.

I think this example is helpful, Thanks in advance !!

"Client Code" "Category Product Code " "Product Code" Sales

001 2 901 101

001 2 518 51

001 2 600 201

001 3 350 301

002 2 901 102

002 2 518 52

002 2 901 202

002 3 350 302

002 4 313 502

In the example above the expected Result:

Sales from the Client 001, are ALL excluded because this costumer has bought also other Product Code from Category 2, that are different from "Product Code" 901 and 518.

Re: Customers who bought 2 Products and nothing else in same category

jonathandienst — Thu, 18 May 2017 09:25:23 GMT

Perhaps

Sum({$

<CLIENT CODE = P({1<[PRODUCT CODE] = {"901","518"}> - 1<[PRODUCT_CODE] -= {"901","518"}>} CLIENT CODE)>

} [SALES])

Re: Customers who bought 2 Products and nothing else in same category

luisccmm — Thu, 18 May 2017 10:55:42 GMT

Thanks Jonathan,

But still is not what I need. On the formula there should be something regarding the Product Category to which Product Code 901 and 518 belong, that on the example above the "Category Product Code" is "2".

Let´s explain it again in other words. I have some clients that could be labeled as "Lovers of Product 901 and 518", if I stop selling products 901 and 518 and I lose this clientes I need to value how much I will lose as a whole - because they buy products from other categories. There could be customers who have bought product 901 or 518 from category 2, but they are not Lovers of Product 901 or 518 if they also have bought other products from category 2, those ones do not interest me on this issue.

The formula should include someway to include the LOVERS of Products 901 and 518 and their whole sales from the data set, but not the ones that do also have bought Products 901 and 518 and others Products from category 2.

"Client Code" "Category Product Code " "Product Code" Sales

001 2 901 101

001 2 518 51

001 2 600 201

001 3 350 301

002 2 901 102

002 2 518 52

002 2 901 202

002 3 350 302

002 4 313 502

In the example above the expected Result:

Sales from the Client 001, are ALL excluded because this costumer has bought also other Product Code (600)from Category 2, that are different from "Product Code" 901 and 518. The expected result in this case will be "0", this is not he LOVER of 518&901 Product Code.

ALL Sales from the Client 002, should ALL be INCLUDED, because this customer has not bought other product codes different from 901 and 518 from category 2. This client is the LOVER of Product 901 and 518. All sales from customer 002 are included, this also included sales from other categories so: 102 +52 +202 +302+502

Re: Customers who bought 2 Products and nothing else in same category

felipedl — Thu, 18 May 2017 14:06:00 GMT

Hi Luis,

You could work this out by creating additional flags to catch what you must show or not in the load script.

See the attached file for a solution.

Best regards,

Felipe.

Re: Customers who bought 2 Products and nothing else in same category

luisccmm — Thu, 18 May 2017 14:51:39 GMT

Dear Felip,

It looks like the app is getting the expected Result (Total Value) but I need a "set analysis formula" for qlik sense, and I don´t see how to impliment that solution. I am an end business user, not a developer, and need a formula to get the result on set analysis for qlik sense.

I am using Qlik Sense 3.2

Regards

Re: Customers who bought 2 Products and nothing else in same category

luisccmm — Fri, 19 May 2017 10:42:27 GMT

A jobmate has give me a set analysis solution:

sum( {1<Client Code = P({1<[Produc Code]={"901","518"}>} ICC)- P({1<Product Code]=-{"518","901"},CategoryCode={"2*"}>} ICC)>} [Sales] )

They key of that formula above come from a combination of two P functions:

- P({1<Product Code]= - {"518","901"},CategoryCode={"2*"}>}ICC)

Just to explain this combination of two P Functions.

1st part:

P({1<[Produc Category Code]={"901","518"}>} ICC)-

That makes a LIST of POSSIBLE clients that have a Product code 901 or 518 in the data set.

2nd Part.

P({1<Product Code]= - {"518","901"},CategoryCode={"2*"}>}ICC)

That makes a LIST of POSSIBLE clients from CategoryCode 2 that have any other Product Code Different from 518 and 901 (Different from but not meaning excluding the ones that have 518 or 901 with any other Product Code from Category2).

COMENT:: From the sencond part "=-" and "-=" notation are both are working and both are producing the same result. But as Jhonathan Dienst has point out the correct form is "-="

jontydkpi‌

So acording to him the correct form will be:

P({1<Product Code] -={"518","901"},CategoryCode={"2*"}>}ICC)

Result:

This produces a combination of two list of POSSIBLE clients. The clients that are included in the first list are eliminated with the ones that match with the second list.

Thanks everybody for they Help!!!

Regards

Re: Customers who bought 2 Products and nothing else in same category

jonathandienst — Mon, 22 May 2017 05:42:13 GMT

Are you sure that this expression is correct? The character sequence =- is not a valid set operator and I suspect that the relevant clause is being ignored. Or is that a typo (as it should read -= )?

In QV 11.2 the sequence =- appears to work, but this could be a bug, which could be fixed at some later point.

Re: Customers who bought 2 Products and nothing else in same category

luisccmm — Tue, 23 May 2017 13:27:18 GMT

I have check "=-" and "-=" and both are working and both are producing the same result, - I am using Qlik sense 3.1 seversuits 10.3.5