1 Reply Latest reply on Nov 20, 2013 4:05 PM by Tracy Rodgers

    Calculate the grand total

    parvinder.bindra.0

      Hi All,

       

      I am struggling on 2 issues here.

       

      Currently I have layout on the worksheet is in the following way

       

      Base Alt_Part     Min   Max     QOH

      ABI     H10-30     3     3               4

      ABI     H10-40     5     5               2

      DFW   H10-30     2     2               5

      DFW   H10-40    8     8               7

      LAX     H10-30     9     9              10

      LAX     H10-40     6     6              12

      Totals                                        40

       

      However, Expected Way

       

      Base     Alt_Part     Min     Max     QOH    

      ABI        H10-30       5          5          4

      ABI        H10-40                              2

      DFW      H10-30      8          8          5

      DFW      H10-40                              7

      LAX        H10-30     9          9          10

      LAX        H10-40                             12

      Total                      22         22         40

           

      1. User does not want Min Max values to repeat when the BASE is same in the next record, though alt_Part is different.

      Cordially it should be good to get maximum of the values for the same BASE, eg in this case for ABI its 3 for both the parts H10-30 and H10-40.

      2. And we need to get totals of the Min Max columns at the bottom as well ( I am not able to get that)

       

      Workbook attached.

       

      Would really appreciate if anybody can help me with this.

       

      Thanks,

      Parvinder

        • 1. Re: Calculate the grand total
          Tracy Rodgers

          Hi Parvinder,

           

          To get the columns to only show the first value for each Base type for MIN and MAX, create two calculations similar to the following:

           

          ifnull((if index()=1 then str(lookup(min([MIN]), 1)) else ' ' end), str(lookup(min(MIN),0)))

           

          ifnull((if index()=1 then str(lookup(max([MAX]), 1)) else ' ' end), str(lookup(max([MAX]),0)))

           

          Then, place these on the rows shelf between Alt Part and PART_TYPE. Right click on each calculation and select Compute Using...>Alt Part.

           

          Since these are discrete, it's not possible to add grand totals. One option may be to find the desired totals and place them on a separate worksheet, then place them all on a dashboard together.

           

          Hope this helps!

           

          -Tracy