
Re: Aggr, Max and Sum
Anil Samineni Jan 16, 2017 10:10 PM (in response to Henrique Demarco)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
Henrique Demarco Jan 17, 2017 5:01 AM (in response to Anil Samineni )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
Henrique Demarco Jan 17, 2017 5:25 AM (in response to Henrique Demarco)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
Prem Utukuri Jan 17, 2017 5:55 AM (in response to Henrique Demarco)try this once
Max(Aggr(dimension,expression))
Max(Aggr(time,saledept))


Re: Aggr, Max and Sum
Henrique Demarco Jan 17, 2017 11:21 AM (in response to Andrey Khoronenko)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
Henrique Demarco Jan 17, 2017 10:49 AM (in response to Prem Utukuri)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 Jan 17, 2017 11:08 AM (in response to Henrique Demarco)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
Henrique Demarco Jan 17, 2017 11:22 AM (in response to Sunny Talwar )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
Andrey Khoronenko Jan 19, 2017 3:13 AM (in response to Henrique Demarco)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




Re: Aggr, Max and Sum
Sunny Talwar Jan 17, 2017 7:29 AM (in response to Henrique Demarco)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

Comercial  Copy.qvw 1.9 MB

Re: Aggr, Max and Sum
Henrique Demarco Jan 17, 2017 12:34 PM (in response to Sunny Talwar )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
Henrique Demarco Jan 17, 2017 1:25 PM (in response to Henrique Demarco)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 Jan 17, 2017 1:50 PM (in response to Henrique Demarco)=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))

Comercial  Copy.qvw 1.9 MB

