Aggr: Latest status based on day selection

[#1]

Hello,

I have the following data:

Order_ID	Action_ID	Day	Status
A	10	1	Open
A	11	2	Open
B	20	1	Open
B	21	2	Open
B	22	3	Open
C	30	1	Open
C	31	2	Closed
C	32	3	Open
C	33	4	Closed
D	40	1	Open
D	41	2	Open
D	42	3	Closed
E	50	1	Open
E	51	2	Closed
E	52	5	Open

On each Order, a user can make an action which may change the Status of the Order.

Example: On Day 5 the user made an action (Action_ID = 52) on Order 'E' which changed its status from 'Closed' to 'Open'.

I want to create a bar graph with Status as a Dimension which counts the Orders based on the latest action made and depending on the Day selection.

Example:

If I select Days 1,2,3,4, the Graph should behave as follow:

Open = 2

• +1 for Order_ID = A and Action_ID = 11 (latest action made on order A within Day selection)
• +1 for Order_ID = B and Action_ID = 22 (latest action made on order B within Day selection)

Closed = 3

• +1 for Order_ID = C and Action_ID = 33 (latest action made on order C within Day selection)
• +1 for Order_ID = D and Action_ID = 42 (latest action made on order D within Day selection. Action 31 is not counted because not the latest action made)
• +1 for Order_ID = E and Action_ID = 51 (latest action made on order E within Day selection. Action 52 is not counted in the "Open" because it was made on day 5 which is out of the selection)

So I built the below expression:

=count(aggr(Max(Action_ID), Order_ID))

I assume it works that way:

1. Aggr builds a temp table which get the latest Action made on each order within the selection made on Day
2. Count the Action_ID (hence the orders) depending on the status thanks to the graph dimension

It doesn't work so I guess my assumptions above are incorrect.

Somebody would have an idea?

[tresesco]

Not really sure about the requirement. Why C should not be counted for status 'Open' and max Action_ID=32? It seems that you are counting all Order_IDs once within scope. Then can try like:

=Count(DISTINCT Order_ID)

Else, you might have to explain in a simpler way.

[#3]

tresesco a écrit:

Why C should not be counted for status 'Open' and max Action_ID=32?

Because Action_ID = 32 is not the latest action made on Order C. Latest action is Action_ID=33 made on day 4 (part of the selection) so I should only look at this particular one.

If I try to reformulate my requirement in a more functional way:

"I want to count how many orders are Open and how many are Closed as of latest day of my selection on Day"

If I select days 1 to 4 ("I want to count Orders per Status as of day 4"):

Order A is Open as of day 4 (nothing happened since day 2)

Order B is Open as of day 4 (nothing happened since day 3)

Order C is Closed as of day 4 (it was closed on day 4)

Order D is Closed as of day 4 (nothing happened since day 3)

Order E is Closed as of day 4 (it was closed on day 4)

If I select days 1 to 5 ("I want to count Orders per Status as of day 5"):

Nothing will change for Orders A, B, C and D (no action/status change on day 5).

Order E will be counted as Open instead of Closed because an action performed on day 5 (Action_ID=52) switched its status from Closed to Open

Hope it clarifies.

[#4]

I build a small table to verify my assumption 1: "Aggr builds a temp table which get the latest Action made on each order within the selection made on Day"

It seems to work and properly adjust to selections on Day (If I select from Day 1 up to Day 5 I get Action_ID 55 for E):

Now I need to be able to count the orders and dispatch per Status but the expression [ =count(aggr(Max(Action_ID), Order_ID)) ] does not work: It does not take into account the graph dimension (Status) to dispatch:

Any idea?

[Clever_Anjos]

""I want to count how many orders are Open and how many are Closed as of latest day of my selection on Day""

maybe this can run:

sum(if(aggr(firstsortedvalue(Status,-Day),ActionID)='Open',1,0)  // Open

sum(if(aggr(firstsortedvalue(Status,-Day),ActionID)='Closed',1,0)  // Closed

[#6]

It indeed works:

sum(if(aggr(firstsortedvalue(Status,-Day),Order_ID)='Open',1,0))
sum(if(aggr(firstsortedvalue(Status,-Day),Order_ID)='Closed',1,0))

If I understand correctly instead of retrieving latest action and deduce the latest status from it, you directly get the latest status using FirstSortedValue within aggr using Day as the weight.

Any idea why it doesn't work if I put Status as the graph dimension and keep one expression only (remove the if testing Status value) ?

Aggr is supposed to be dimension sensitive

sum(aggr(firstsortedvalue(Status,-Day),Order_ID))

[Clever_Anjos]

"If I understand correctly instead of retrieving latest action and deduce the latest status from it, you directly get the latest status using FirstSortedValue within aggr using Day as the weight" Correct!

I don´t know why does not work the other way. Could you post a sample? I´ll do some tests

[#8]

Here you go!

I tried to implement your solution in the my real app (datamodel much more complicated) and I get wrong results

Need to investigate further...

[Clever_Anjos]

Checking here:

count(aggr(firstsortedvalue(Status,-Day),Order_ID,Status)) returns values, but they´re not correct, because we need to count an Order_ID only once. Using a dimension, they´re counted twice (one last value to each Status)

I don´t see right now a way of doing what you want, using Status as a dimension, sorry