Display all data of sub table

[g_f_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.

[Gysbert_Wassenaar]

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

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

[g_f_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.