Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

h_demarco
New Contributor III

Aggr, Max and Sum

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

Tags (2)
14 Replies

Re: Aggr, Max and Sum

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?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
h_demarco
New Contributor III

Re: Aggr, Max and Sum

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.

h_demarco
New Contributor III

Re: Aggr, Max and Sum

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.

Not applicable

Re: Aggr, Max and Sum

try this once

Max(Aggr(dimension,expression))

Max(Aggr(time,saledept))

ahaahaaha
Honored Contributor

Re: Aggr, Max and Sum

Hi Henrique,


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

1.jpg

and with expression


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


2.jpg


we obtain the following chart.

3.jpg

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

Regards,

Andrey

Re: Aggr, Max and Sum

Are you hoping to see this?

Capture.PNG

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

Capture.PNG

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

Capture.PNG

h_demarco
New Contributor III

Re: Aggr, Max and Sum

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

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

h_demarco
New Contributor III

Re: Aggr, Max and Sum

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:

Capture.JPG

Chart should be something like that:

Capture2.JPG

Thanks for your help and quick answer.

Henrique