If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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??
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 "-="
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
Hi Luis,
See the attached file to see if it helps.
Best regards,
Felipe. !
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
check my sample app here, this should give you some insight
Re: Nested Set Analysis - Customer bought in both time periods
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
Perhaps
Sum({$
<CLIENT CODE = P({1<[PRODUCT CODE] = {"901","518"}> - 1<[PRODUCT_CODE] -= {"901","518"}>} CLIENT CODE)>
} [SALES])
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
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.
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
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 "-="
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