2 Replies Latest reply: Nov 27, 2012 9:35 AM by Gerard Bakker RSS

    Display all data of sub table

    Gerard Bakker

      Hi everyone,

       

      I have a problem with displaying all values of targets in a sub table. The master table has data with must be compared with the target data in the linked sub table.

       

      When a seller is displayed with the revenue from the master table, then all the target data from the sub table must stay visible. This must also be the case for filtering in the YEAR or PERIOD from the master table.

       

      For example, when filtering on SALESPERSON = GFB, YEAR = 2012 an QUARTER = 2 then Revenue must be € 730,00 and Target must be € 210,00.

       

      In my example the master table is rather simple, but my real master table is complicated with approx. a million records. To put the target table as master table is no option in my opinion.

       

      See my attachments for support.

       

      Please, can anyone help me?

       

      Best regards,

      Gerard.

        • Re: Display all data of sub table
          Gysbert Wassenaar

          You want the sum of the max values for the period? If so try:

          =Sum(aggr(max(TARGET),PERIOD))

            • Re: Display all data of sub table
              Gerard Bakker

              Hi Gysbert,

               

              What I want is the sum(TARGET) for the Period selection. If the revenue table doesn’t have all the periods and the selection is a whole year, then the sum (TARGET) must be shown for the whole year. When the selection is of some periods then the target must show the sum over these periods.

               

              My table link key is not good (I think), but when I keep more then one column the same name then I have synthetic tables.

               

              In short:

              When the revenue table doesn’t have records for a sales person in period 6 and the target table has, then the target must be shown for period 6. This off course when QUARTER = 2. I think the table links must be altered to accomplish this, but I don't know how.