Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Closed = 3
So I built the below expression:
=count(aggr(Max(Action_ID), Order_ID))
I assume it works that way:
It doesn't work so I guess my assumptions above are incorrect.
Somebody would have an idea?
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.
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.
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?
""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
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))
"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
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...
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