14 Replies Latest reply: Jan 19, 2017 3:13 AM by Andrey Khoronenko RSS

    Aggr, Max and Sum

    Henrique Demarco

      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

          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

              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

              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

                  try this once

                  Max(Aggr(dimension,expression))

                   

                  Max(Aggr(time,saledept))

                    • Re: Aggr, Max and Sum
                      Andrey Khoronenko

                      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
                          Henrique Demarco

                          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

                        • Re: Aggr, Max and Sum
                          Henrique Demarco

                          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

                              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
                                Andrey Khoronenko

                                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


                                4.jpg


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


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


                                5.jpg

                                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

                            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

                              • Re: Aggr, Max and Sum
                                Henrique Demarco

                                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

                                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.