double aggregation in one formula?

[ziabobaz]

Hello,

I have DATA table: ORDER with corresponding STATUS. Status is DUAL.

If the last status per order is not equal "order-delivered", than the ORDER is in progress. The maximum status for this order is considered status in progress

I want to know how many statuses in progress I have (in other words, for all my undelivered orders I want to know of which stage they are in at the moment). I have Date dimension, but i will disregard it for simplicity.

I managed to build aggregation table:

aggr(

{<

OrderN=

P({<status={"order-created"}>})-

P({<status={"order-delivered"}>})

>}

max(status),OrderN)

But I can't figure out how to build the table, 'what i need'

I need it in this exact way as in screenshot (with one Dimension = status, and one Measure).

Is it possible?

Thank you

[Nicole-Smith]

I would flag your max status in the load script, and then add up that flag:

Data:
LOAD order, dual(status, num_status) as status INLINE [
order, status, num_status
100, order-created, 1
100, order-ready, 2
100, order-confirmed, 3
200, order-created, 1
200, order-ready, 2
300, order-created, 1
400, order-created, 1
400, order-ready, 2
400, order-confirmed, 3
400, order-sale, 4
400, order-delivered, 5
];

LEFT JOIN (Data)
LOAD order, max(status) as status, 1 as max_status
RESIDENT Data
GROUP BY order;

Then on your chart:

Dimension: status

Expression: sum(max_status)

For the example data that you gave above, it would return this:

status	sum(max_status)
order-created	1
order-ready	1
order-confirmed	1
order-delivered	1

[Anonymous · Accepted solution]

Hi Глеб

you can try something like this

Expression:

sum(

    Aggr(

        If(status =  Max({< OrderN -= p( {1<status  ={"order-delivered"}>} ) >} TOTAL <OrderN> status) , 1, 0)

    , OrderN, status)

)

Script:

LOAD OrderN, dual(status, num_status) as status, num_status INLINE [

OrderN, status, num_status

100, order-created, 1

100, order-ready, 2

100, order-confirmed, 3

200, order-created, 1

200, order-ready, 2

300, order-created, 1

400, order-created, 1

400, order-ready, 2

400, order-confirmed, 3

400, order-sale, 4

400, order-delivered, 5

500, order-created, 1

500, order-ready, 2

500, order-confirmed, 3

];

[Anonymous]

And an interesting thing, replacing sum by count, we get

//Sum(

Count(

    Aggr(

        If(status =  Max({< OrderN -= p( {<status  ={"order-delivered"}>}) >} TOTAL <OrderN> status) , 1, 0)

    , OrderN, status)

)

[ziabobaz]

Max,

I can't seem to replicate, although the result is very close.

Could you please clarify couple of points below?

sum(

    Aggr(

        If(status =  Max(

        {<OrderN=

P({<status={"order-created"}>}) // I need this one because there are other orders in my data which were created earlier (not in the selected DATE period) - I don't want to count them

-

P({<cancel_flag={'Y'}>}) // some of them were cancelled, i don't want to count them as well

-

P({<status={"order-delivered"}>})

>}

        TOTAL <OrderN> status) // cool thing with <OrderN> - is it equivalent to 'find me max of status by OrderN, disregarding Status?

, 1, 0) // here i don't understand - you end up with aggr( [1 or 0], OrderN, status), which contradicts to the help file, where you can use only 2 inputs to the formula. What does [1 or 0] do?

    , OrderN, status) // OrderN is without a function, what is the aggregation here?

    )

Thank you!

[ziabobaz]

it would have worked if i had data without DATE

in my case the FLAG is dynamic - it can be 1 for one period, but 0 for another

[Anonymous]

Привет, Глеб еще раз))

        TOTAL <OrderN> status) // cool thing with <OrderN> - is it equivalent to 'find me max of status by OrderN, disregarding Status?

Quite right,         Max(TOTAL <OrderN> status)      gives  you

and when you tell      If(status =  Max( TOTAL <OrderN> status) , 1, 0)         the result is

all other statuses for every OrderN are equalent 0 . Then you just aggrigate this function and  sum "ones". I can not explain the aggregation function in this example, but without it the sum does not work)

[Anonymous]

I would say aggregation is used here only because it gives the opportunity to use sum() over max() function.

[ziabobaz]

Max,

The script is working, thank you!

It seems that AGGR can agregate over 2 fields (OrderN, status), although not explained in Help.

The only thing I am trying to solve (if you have thought, please share) - I now know what was the last successfull status my order was stopped at. However, the correct way to show is the status in progress. As per your last table, that would mean adding +1  to Status field. I can't replace the Dimension (with Dim+1) as I have other formulas tied to it (orders cancelled and orders completed).

Спасибо!

[ziabobaz]

...which is achieved by simple above() function, actually )

solved!