topic Re: Aggr, Max and Sum in QlikView

Aggr, Max and Sum

h_demarco — Mon, 16 Jan 2017 20:21:06 GMT

Dear Qlik colleagues,

File attached is calculating the total time of sales order flow inside company I work for.

We have average time and total time. The second gets the worst case on each department (Logistics, Sales-Comercial and Financial). But we desire to get the longer Sales Order (Pedido) instead of longer on each department.

For example: in out/2015 the longer case in Sales dept took 1174h56min and it was PEDIDO=102365. The longer case in Financial dept took 846h36min and it was PEDIDO=102663. The longer case in Logistics dept took 1448h05min and it was PEDIDO=102297.

But the question is: which was the longer PEDIDO in all 3 sectors (from beginning to end of flow)?

I already tried some formulas with Max, Aggr and Sum, but maybe it is not this way.

Does anyone have a solution?

Thanks and best regards,

Henrique

Re: Aggr, Max and Sum

Anil_Babu_Samineni — Tue, 17 Jan 2017 03:10:10 GMT

Henrique Demarco wrote:

But the question is: which was the longer PEDIDO in all 3 sectors (from beginning to end of flow)?

Somewhat, I understand the content, Can you describe more for this part?

Re: Aggr, Max and Sum

h_demarco — Tue, 17 Jan 2017 10:01:12 GMT

Yes. Might be some misunderstanding due to translation.

PEDIDO is the field name and means Sales Order number.

All Sales Order starts in Comercial (Sales Dept), then some of them goes to Financial and ends in Logistics.

For longest PEDIDO in all 3 sectors I mean longest Sales Order for complete flow, that goes from Comercial to Logistics.

Re: Aggr, Max and Sum

h_demarco — Tue, 17 Jan 2017 10:25:50 GMT

A workaround for it can be to create a field in script that would calculate interval between PEDIDO (Sales Order) begin and end. With interval it would be possible to find longest PEDIDO and then plot it.

I always try to avoid this kind of solution to save memory and speed up qvw, but can be a solution.

Hope someone can help.

Re: Aggr, Max and Sum

Tue, 17 Jan 2017 10:55:35 GMT

try this once

Max(Aggr(dimension,expression))

Max(Aggr(time,saledept))

Re: Aggr, Max and Sum

ahaahaaha — Tue, 17 Jan 2017 12:21:26 GMT

Hi Henrique,

if you build a chat direct table with dimensions Perdido and Setor

and with expression

If (rank(Max(TOTAL <PEDIDO, Setor> Tempo_total))=1, Max(TOTAL <PEDIDO, Setor> Tempo_total))

we obtain the following chart.

Is this what you want? Maybe this will help solve your problem?

Regards,

Andrey

Re: Aggr, Max and Sum

sunny_talwar — Tue, 17 Jan 2017 12:29:29 GMT

Are you hoping to see this?

Or are you hoping to see just the Green portion since it is the max among the three sector?

Expression for the second one is this

=If(Max(TOTAL <$(='[' & GetCurrentField([Tempo]) &']')> Aggr(DISTINCT Sum({<Situação_Fluxo-={'11'}>} Tempo_total), Status_Fluxo, PEDIDO)) =

Max(Aggr(DISTINCT Sum({<Situação_Fluxo-={'11'}>} Tempo_total), Status_Fluxo, PEDIDO)),

Max(Aggr(DISTINCT Sum({<Situação_Fluxo-={'11'}>} Tempo_total), Status_Fluxo, PEDIDO)))

Without selection, it looks like this

Re: Aggr, Max and Sum

h_demarco — Tue, 17 Jan 2017 15:49:46 GMT

Aggr syntax is:

aggr ([ distinct | nodistinct ] [{set_expression}]expression {, dimension})

I already tried this solution without success.

Thanks for your help.

Re: Aggr, Max and Sum

sunny_talwar — Tue, 17 Jan 2017 16:08:39 GMT

My proposed solution might not be the one you wanted, but did you get a chance to look at it?

Re: Aggr, Max and Sum

h_demarco — Tue, 17 Jan 2017 16:21:39 GMT

Hi Andrey,

Your solution is returning the worst (if you consider that a long lead time is not good for business) department for each Pedido (Sales Order).

I am looking for the worst Sales Order in a defined period time, like October 2015.

Lets work with Logistics and Oct 2015. The longest Sales Order for Logistics is 102297, which took 1448:05:38 total time. But is this the longest Sales Order of the company? No.

The longest SO for the company is 102663, that took 933:00:12 in Comercial dept and 846:36:46 in Financial and counting, because it did not get to Logistics yet.

Remember SO's flow is Comercial -> Financial -> Logistics.

If you see SO 102663 is not the worst case among 3 departments, but is the worst case of the company.

In my case, I would like to plot the worst company case with total time on each department. In case of 102663, Logistics would not appear in chart because it has 0min of lead time.

Here an example in a Excel pivot table:

Chart should be something like that:

Thanks for your help and quick answer.

Henrique

Re: Aggr, Max and Sum

h_demarco — Tue, 17 Jan 2017 16:22:47 GMT

Not yet, but I will check your proposal in a few minutes.

By the way, thanks for your quick reply.

Re: Aggr, Max and Sum

h_demarco — Tue, 17 Jan 2017 17:34:28 GMT

Hi Sunny,

It is not exactly what I need. Your solution is returning total time in the department that took longest lead time.

Please, have a look at my answer to Andrey Khoronenko on 17/01/2017 11:21.

As you guys are returning suggestions to me, I think problem is getting clear.

Thanks for your help, once again.

Henrique

Re: Aggr, Max and Sum

h_demarco — Tue, 17 Jan 2017 17:52:27 GMT

Folks,

With some of your suggestions the expression

=max(aggr(Sum({<Situação_Fluxo-={'11'}>} Tempo_total),PEDIDO))

is returning the number that I am looking for.

Re: Aggr, Max and Sum

sunny_talwar — Tue, 17 Jan 2017 18:50:54 GMT

How about this?

=Sum(Aggr(If(PEDIDO = FirstSortedValue(TOTAL <$(='[' & GetCurrentField([Tempo]) & ']')> PEDIDO, -Aggr(Sum({<Situação_Fluxo-={'11'}>} Tempo_total), PEDIDO)), Sum({<Situação_Fluxo-={'11'}>} Tempo_total)), PEDIDO, $(='[' & GetCurrentField([Tempo]) & ']'), Setor))

Re: Aggr, Max and Sum

ahaahaaha — Thu, 19 Jan 2017 08:13:39 GMT

Hi Henrique,

First, I created a variable varMaxVolume, which calculates the maximum total time of the sample (if the sample is not present, the maximum time is calculated from all the data). It contains the expression

=Max (Aggr(Sum(Tempo_total), PEDIDO))

Next, build a table with dimension PEDIDO and expressions

1) Comercial: Sum({<PEDIDO= {"=Num(Sum(Tempo_total))=varMaxVolume"}, Setor={'Comercial'}>} Tempo_total)

2) Financeiro: Sum({<PEDIDO= {"=Num(Sum(Tempo_total))=varMaxVolume"}, Setor={'Financeiro'}>} Tempo_total)

3) Logística: Sum({<PEDIDO= {"=Num(Sum(Tempo_total))=varMaxVolume"}, Setor={'Logística'}>} Tempo_total)

4) Grand Total: Num(Sum({<PEDIDO= {"=Num(Sum(Tempo_total))=varMaxVolume"}>}Tempo_total))

We get a table with a single line, with a maximum value of Grand Total in the sample or all data

Now you can build a histogram. Dimensions PEDIDO and Setor, expression

Num(Sum({<PEDIDO= {"=Num(Sum(Tempo_total))=varMaxVolume"}>}Tempo_total))

Of course the parameters and settings of the histogram can be done in accordance with the tasks. Like this is what we were looking for. I am glad if I can be of any help to you.

Regards,

Andrey