Hello
I have to filter the clients that buy 3 items A, B and C together. The filters do not work because they bring customers who buy 1 or 2 or 3 items, but not only those who buy all 3. How could I do it? thanks
Hi @sanmar -
This is an interesting one, there’s no multi-select parameter/filter for ‘match all’.
Here’s a workaround that utilizes @darcoli 's multi select parameter action
Step 1 - Create a ‘SelectedItemIDs’ parameter with a default value of ‘,’
Step 2 - Create a ‘c_updated_selected_items_id’ calculated field and add to a table visual group by.
ifelse(
locate(${SelectedItemIDs}, {item_id}) > 0, replace(${SelectedItemIDs}, concat({item_id},','), ''),
concat(${SelectedItemIDs},{item_id},',')
)
Step 3 - Create ‘c_in_selected_item_ids_flg’ calculated field and use in conditional formatting.
ifelse(locate(${SelectedItemIDs}, {item_id} ) > 0 , 1, 0)
Step 4 - Add ‘c_all_items_flg’ calculate field to use as filter.
// hard coded 10 matches
ifelse(
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',2),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',3),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',4),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',5),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',6),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',7),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',8),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',9),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',10),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',11),1,0),[{client_id}],PRE_AGG)
=strlen(replace(${SelectedItemIDs},',','')),'Y','N')
Result:
1 Like
Hi robdhondt
Regarding step 4: I would need to count the customers -without repetition- that meet the criteria of buying all the items together Could you help me with this?thanks in advance
Hi @sanmar -
Sure. QuickSight has some helpful If functions, you can use distinct_countIf using the “c_all_items_flg” calculated field you created in step 4.
Example:
Note: distinct_countIf is the same as doing distinct_count(ifelse({c_all_items_flg}='Y',{client_id},null)) but this helpful function keeps things a bit cleaner.
Hi @sanmar -
You will get that error message if you didn’t set “,” as the default value for the parameter. The functions throw the error because the expression is null. Is one set?
Hi @sanmar -
Can you paste the actual calculated field? It’s odd that you are getting the error.
I would try working backwards through the calculated field and reducing the complexity until you find the section causing the error.
For example, change the strlen section to a static integer. If the error goes away you know it has something to do with that section, etc
// hard coded 10 matches
ifelse(
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',2),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',3),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',4),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',5),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',6),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',7),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',8),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',9),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',10),1,0),[{client_id}],PRE_AGG)+
maxOver(ifelse({item_id}=split(${SelectedItemIDs},',',11),1,0),[{client_id}],PRE_AGG)=10,'Y','N')
Then try the inverse, and see if the error goes away. This is how I typically debug lengthly/complex calculated fields.
ifelse(10=strlen(replace(${SelectedItemIDs},',','')),'Y','N')
Hi robdhondt
I don’t understand the meaning… What is the “actual calculated field”? could you give me an example please? Thank you
ifelse (
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,2),1,0),[{codcliente}],PRE_AGG+
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,3),1,0),[{codcliente}],PRE_AGG+
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,4),1,0),[{codcliente}],PRE_AGG+
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,5),1,0),[{codcliente}],PRE_AGG+
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,6),1,0),[{codcliente}],PRE_AGG+
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,7),1,0),[{codcliente}],PRE_AGG+
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,8),1,0),[{codcliente}],PRE_AGG+
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,9),1,0),[{codcliente}],PRE_AGG+
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,10),1,0),[{codcliente}],PRE_AGG+
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,11),1,0),[{codcliente}],PRE_AGG
=strlen(replace(${selecteditemsids},‘,’,‘’)),‘Y’,‘N’)
@sanmar -
I missed it in your original image. You are missing the closing parenthesis in all your maxOver functions.
Like this.
ifelse(
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,2),1,0),[{codcliente}],PRE_AGG ) +
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,3),1,0),[{codcliente}],PRE_AGG ) +
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,4),1,0),[{codcliente}],PRE_AGG ) +
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,5),1,0),[{codcliente}],PRE_AGG ) +
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,6),1,0),[{codcliente}],PRE_AGG ) +
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,7),1,0),[{codcliente}],PRE_AGG ) +
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,8),1,0),[{codcliente}],PRE_AGG ) +
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,9),1,0),[{codcliente}],PRE_AGG ) +
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,10),1,0),[{codcliente}],PRE_AGG ) +
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,11),1,0),[{codcliente}],PRE_AGG )
=strlen(replace(${selecteditemsids},‘,’,‘’)),‘Y’,‘N’)
@robdhondt Hi , thank you very much, it worked! But I didn’t get what I need. I used this: distinct_countIf({codcliente}, {c_all_items_flg}=‘Y’) to count clients
In the exercise that I did, I took 3 product codes: A, B and G. I need to find how many customers bought the 3 codes (together) in a period of time (month, quarter and year). What I get with this calculated field is the number of customers who bought any of the 3 (=120 clients/month), but not only those who bought all 3 (=just 2 clients/month !!). Maybe I’m doing something wrong?, I don’t know… Thanks
Hi @sanmar -
The way the LAA calculation is partitioned by ‘codcliente’. Which means if the client ever purchased, if you want check by (month, quarter, and year) you need to add that to the partition argument.
example:
maxOver(ifelse({codsku}=split(${selecteditemsids},‘,’,2),1,0),[{codcliente},{date_grain}],PRE_AGG)
robdhondt
Hello, Could you please tell me a bit about each part of this calculated field? So that way I can understand it, and I can adapt it correctly to what I need. Thank you!!
Hi @sanmar -
The QuickSight docs for maxOver explain this really well see maxOver - Amazon QuickSight
And for some more real life examples check out this blog Calculated fields, level-aware aggregations, and evaluation order in Amazon QuickSight | AWS Big Data Blog
The LAA calculations are easier to understand if you have SQL/windowing functions experience. You can think of it as a “window” into your dataset. In the case you want to look at subset that is just your client and period of time.
Hi
I’m not a programmer and I don’t have experience with SQL. I thought maybe I’d find an easier solution to this issue here. Well I’ll keep looking then. Cheers
@robdhondt Hi
I was finally able to do what you suggested I ask you:
-
when I add the filter ‘c_all_items_flg’ and choose the codskus, I get a message that “No data” No data found for the visual element. Not one, not two, three codsku works
-
How should I prepare this filter ‘c_all_items_flg’ to filter the group of 3 codsku?thanks
