Item combination in QUicksight

Question & Answer
, ,
1

Hello,
I have a datasetr with the following columns:
ordernumber, item number,picked date.
I want to calculate how many times 2 or 3 items are sold togheter in the same ordernumber.
this could help to understand the sinergy among items.
While I could do this easily in python, I need to do the same calulation in QS since it will be nice to have the filter on the picked date.
In python I can filter the date, do the calculations and upload the df in QS and show that.

but I can not select specific timeframes.

any suggestion?
thank you

2

hello @remba87 hope this message finds you well

if I understand your problem, my suggestion is utilise a formula to tally the number of items associated with each ordernumber. For instance, you might employ the following expression: countOver({item number}, [{ordernumber}], PRE_AGG). This will generate a field that enumerates the items linked to each ordernumber

hope this help you

1 Like
3

@remba87, you could

  1. create a table with orderNumber in Group By section
  2. create 3 parameters: itemNumber1, itemNumber2, itemNumber3
  3. create 3 visuals with dropdowns to select values for these parameters (NotSelected - default value)
  4. create calculated field AllItemsAreUsedTogather, something like this, I did not test it:

ifelse(
distinct_count( itemNumber) = (
ifelse(itemNumber1<>‘NotSelected’,1,0)+
ifelse(itemNumber2<>‘NotSelected’,1,0)+
ifelse(itemNumber3<>‘NotSelected’,1,0)
),
1,
0)

  1. add 3 columns “item Number 1” = itemNumber1 , “item Number 2”=…
  2. add a filter: “item number” = itemNumber1 or “item Number 1”=NotSelected OR “item number” = itemNumber2 or “item Number 2”=NotSelected OR “item number” = itemNumber3 or “item Number 3”=NotSelected
  3. add filter AllItemsAreUsedTogather=1
    The main idea: when you set item numbers for 2 parameters we show only orders where bot of them were found.

It’s not an ideal solution, just an idea, you should come up with a tradeoff between usability and simplicity.

P.S. and add a “Date Between” filter.

1 Like
4

Hello @lary_andr @Hrolol ,

thank you for your help…however I think that what I want it is really not possible in QS.
this is what I would liek to have at the end

image
image1286×832 56.2 KB

I think the only solution is to do that outside QS and then show it in QS

5

I believe you can implement it using a Pivot visual (with aggregating turned on) using Order in a Group By section, Item Name or Item Code as a column name and a column with hardcoded 1 as a value. You also will have to use window function distinctCountOver() (as far as I remember they are in the section Table Functions in QuickSight) instead of distinct_count(). It should be something like this: distinctCountOver(itemNumber, [orderNumber], PRE_AGG).

There are many ways to implement this, just chose the one that gives you better result/efforts rate

6

Hi @remba87,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

7

Hi @remba87,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!