Customers who bought 2 Products and nothing else in same category

[luisccmm]

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??

[felipedl]

Hi Luis,

See the attached file to see if it helps.

Best regards,

Felipe. !

[luisccmm]

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)

if

(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

[vinieme12]

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

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

[luisccmm]

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.

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

[jonathandienst]

Perhaps

Sum({$

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

} [SALES])

[luisccmm]

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

[felipedl]

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.

[luisccmm]

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

[luisccmm · Accepted solution]

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