Need help with set analysis

[Quy_Nguyen]

Hi everyone,

I have data like this bellow table:

ID	Position	Item	Quantity	OrderTime
1	1	A	1	0
1	2	A	1	2
1	3	B	1	0
1	4	B	1	4
2	5	A	1	1
2	6	B	1	5
2	9	C	1	4
3	7	A	1	0
3	8	A	1	5

This data table is about detail order, we have the quantity and the order time (in minute from the order create time) for each item sold. I want to calculate how many items (total quantity) that have re-purchase in an order after x minutes (x is a time variable)

For example, let's talk about Order ID 3. I have item A was bought 2 times at 0 and 5 mins. I set the time variable to 2 mins. Then I want my expression return the value : 1. I will say that customer bought item A before 2 mins ( <) and another item A after 2mins ( >= ). The return value is the amount of up-sell. If I set the time variable to 0 or 5, the value should return 0.

Another example for the whole table above, I set the time variable to 2 and I want to return 3 for the total quantity of up-sell. ( 2 up-sell in Order 1 and 1 up-sell in Order 3).

This is my current expression, and it returns 4, included one position in the Order2, which is wrong.

vOrderTime = 2

sum({$<ID = p({<[OrderTime] = {"<$(vOrderTime)"}>} [ID])* p({<[OrderTime] = {">=$(vOrderTime)"}>} [ID]),
Item = p({<[OrderTime] = {"<$(vOrderTime)"}>} Item)* p({<[OrderTime] = {">=$(vOrderTime)"}>} Item),
Position = p({<[OrderTime] = {">=$(vOrderTime)"}>} Position)>} Quantity)

I understand the logic is: get all positions which in the same order, having same Item purchased before and after the vOrderTime, then filter positions which have OrderTime >= vOrderTime .

I hope that you got what I am trying to explain. Any help would be appreciated!

[Quy_Nguyen]

@thi_pham @vunguyenq89 

Thank you so much for your help. Both are correct 😊

[rdraeger]

Hi Quy,

based on the amount of data you have, I would not recommend working with if() in the measure.

I have created a solution that works completely with set analysis. I have created a small test application and it seems to work.

To be flexible on the order time, I created a variable named vSlicer and added a slider variable input. vSlicer can have values from 0 to 5 in this scenario.

Now to sum up the quantity of positions with an order time higher than vSlicer for orders that also have positions with an order time less than vSlicer, I created the following formula:

Sum({$<ID = p({<OrderTime ={"<$(vSlicer)"}>}ID) * p({<OrderTime ={">=$(vSlicer)"}>}ID), Position = p({<OrderTime ={">=$(vSlicer)"}>}Position)>}Quantity)

let me explain that in detail:

ID = p({<OrderTime ={"<$(vSlicer)"}>}ID) * p({<OrderTime ={">=$(vSlicer)"}>}ID)

this gives me the intersecting set of all orders (within the given selection), that have items with an order time less than vSlicer and also have items with an order time geater or equal vSlicer.

Position = p({<OrderTime ={">=$(vSlicer)"}>}Position)

this will further limit the results to only positions that have been ordered at or after the desired order time (vSlicer)

If you then sum up the quantity of the resulting set, you will get your up sell quantity.

Of cause this only makes sense, if you select a single item.

Best regards

Robert

[Quy_Nguyen]

Hi Robert,

Thanks for your reply and detailed explanation.  This is shorter than my original expression but still give the same result when selecting only 1 item.

Well you are right about the performance with Aggr and If function. I have to consider about trading off the flexibility of selecting item with the performance on dashboard.