7 Replies Latest reply: Jun 17, 2018 10:12 PM by Jessie Carl Mabalhin RSS

    Roll up data - min, avg and max values

    Jessie Carl Mabalhin

      Hi All,

       

      I am currently working on quotes. When we request for quotes, we get multiple responses/amounts and I am just trying to tie in the min, avg and max amounts to that quote request by rolling up the data.

       

      The data model currently looks like below.

      Data Model.jpg

      The green box is my goal where the MIN_PRICE, AVG_PRICE and MAX_PRICE should be derived by getting the MIN, AVG, and MAX of (ResponseAmount).

       

      I wanted these 3 data rolled up because I am having difficulties when doing pivot tables and charts over a time dimension.

      tables.jpg

       

      Any suggestions, solutions, etc?

       

      Appreciate any help..thanks!

        • Re: Roll up data - min, avg and max values
          Sibusiso Phumelo

          Hi Jessie,

           

          Please find the attached app I hope it helps with your problem as I might not fully understand your problem

           

          Kind Regards

          Sibusiso Phumelo sibusiso90

          • Re: Roll up data - min, avg and max values
            Petter Skjolden

            It is a hard for me to understand exactly what your difficulties are. Doesn't Min(ResponseAmount) and so forth work for you?

             

            What does HeaderID identify really? What does the HeaderVendorID identify? Is it purely the Vendor as such or a combination of a unique Header AND a Vendor? What is the relationship between HeaderID and HeaderVendorID - is it one-to-many or many-to-many?

              • Re: Roll up data - min, avg and max values
                Jessie Carl Mabalhin

                Hi Petter,

                 

                Yeah, I think my issue wasn't too clear.

                 

                So I will start with the data:

                YEARHeaderIDHeaderVendorIDResponseAmount
                201866026133813$2,082.00
                201866026133815$1,170.00
                201866026133820$2,276.40
                201866026133822$1,620.00
                201866026133827$1,963.00
                201866026133828$1,174.00
                201866026133832$1,387.00
                201866026133837$1,722.00
                201866026133845$1,795.00
                201866026133846$2,208.97
                201866026133847$2,026.00
                201866026133848$2,400.00
                201866141134675$235.20
                201866141134678$218.40

                 

                MIN(ResponseAmount), AVG(ResponseAmount), and MAX(ResponseAmount) are OK if I do a pivot table on HeaderID which is displayed below:

                 

                HeaderIDMIN_PRICEAVG_PRICEMAX_PRICE
                66026$1,170.00$1,818.70$2,400.00
                66141$218.40$226.80$235.20

                 

                My aim is to have a SUM of all the MIN prices for the YEAR, Same goes to AVG and MAX. But I end up getting below which is still getting the MIN among the amount and not the SUM of all the minimum amounts.

                 

                What I am getting:

                YEARMIN_PRICEAVG_PRICEMAX_PRICE
                2018$218.40$1,591.28$2,400.00

                 

                Even I put in the totals, I still get this:

                table.jpg

                 

                 

                This is what I want to achieve:

                YEARMIN_PRICEAVG_PRICEMAX_PRICE
                2018$1,388.40$2,045.50

                $2,635.20

                 

                 

                I hope this is clearer now and hopefully there is a simpler solution to achieve this.

                 

                Thanks!

                  • Re: Roll up data - min, avg and max values
                    bruno bertels

                    Hi

                     

                    May be your mesure need and Agrr() function in your pivot table

                     

                    Try this

                    MIN_PRICE

                    min(Aggr(ResponseAmount,HeaderID,Year))


                    AVG_PRICE

                    avg(Aggr(ResponseAmount,HeaderID,Year))


                    MAX_PRICE

                    MAX(Aggr(ResponseAmount,HeaderID,Year))

                    • Re: Roll up data - min, avg and max values
                      Petter Skjolden

                      The short answer is: Sum(Aggr(Avg(ResponseAmount),Year,HeaderID))

                       

                      A more elaborate explanation follows - showing a more flexible technique by using RowNo() and if necessary ColumnNo() (if you pivot out something as additional columns)...

                       

                      You can make an extra check to determine if you are on a TOTALS row in your Pivot Table. That leaves you with full flexibility into putting any type of calculation instead of the "granular"/normal calculation for the totals in your pivot table.

                       

                      This shows which built-in functions that can be used to determine this and how you can employ them in an expression to calculate exactly what you want at the totals rows:

                       

                      2018-06-15 10_12_15-Qlik Sense Desktop.png

                       

                      The first Measures column just shows how the logic works. The next 6 columns shows what RowNo() and ColumnNo() returns so you can use them in an expression to calculate the right measure. Either of the last two columns is what you are aiming at. Why did I include the eigth column at all? The ninth does the job and is simpler. In other cases the same calculation won't do for all the levels and then the eight columns expression shows how to make a completely different calculation for the totals....

                       

                      So columns 1 to 7 of the measures can be deleted and just keep the either of the last two for your final table - the expression for that is:

                       

                      If(RowNo()=0 OR IsNull(RowNo(TOTAL)),Sum(Aggr(Avg(ResponseAmount),YEAR,HeaderID)),Avg(ResponseAmount))

                       

                       

                      FYI the first column in Measures looks like this:

                       

                      2018-06-15 10_04_41-Qlik Sense Desktop.png