Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Creator III

## double aggregation in one formula?

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({})- P({}) >} 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

1 Solution

Accepted Solutions
Anonymous
Not applicable

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-confirmed, 3

200, order-created, 1

300, order-created, 1

400, order-created, 1

400, order-confirmed, 3

400, order-sale, 4

400, order-delivered, 5

500, order-created, 1

500, order-confirmed, 3

];

14 Replies
MVP

```Data:
LOAD order, dual(status, num_status) as status INLINE [
order, status, num_status
100, order-created, 1
100, order-confirmed, 3
200, order-created, 1
300, order-created, 1
400, order-created, 1
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;
```

Dimension: status

Expression: sum(max_status)

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

status sum(max_status)
order-created1
order-confirmed1
order-delivered1
Anonymous
Not applicable

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-confirmed, 3

200, order-created, 1

300, order-created, 1

400, order-created, 1

400, order-confirmed, 3

400, order-sale, 4

400, order-delivered, 5

500, order-created, 1

500, order-confirmed, 3

];

Anonymous
Not applicable

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)

)

Creator III
Author

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!

Creator III
Author

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
Not applicable

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

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
Not applicable

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

Creator III
Author

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).

Спасибо!

Creator III
Author

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

solved!

Tags
Community Browser