Analytics Software

Hi Community,

Let me explain the scenario.

We have a discoverer workbook, which have several reports. Those reports retrieve information from several views built specifically to retrieve information from several tables. Till here, very normal.

Like any other report that you can build from those views, you can select the items (columns) that you will use in order to create the report. Some of these items (columns) are selected with the MAX aggregated function for that item; is that to say, instead of selecting the item profit and then selecting the aggregated function SUM, selects the aggregated function MAX. With this option we can get to narrow the number of records retrieved.

In our case, we have created a report that shows the items that we have in our stock, and for each item, the report shows for each item the family, sum of kilograms, the average price, its value (sum of kilograms x average priice), and we want that the report show for each item (remember that each item is a record or line in the report) the highest number of transaction_id (which it has been selected using the MAX aggregated function for that selected item) but for some kind of transaction types.

Lets see an example:

Family | Product Name | Sum of Kgs. | Units | Avg. Price | Stock Value | Month | Year | Transaction id | Transaction Type | Movement Date
4420 | ALUMINA PS-M BB720 | 97.680,000 | KG | ,44737 | 43.699,10 | 04 | 10 | 7740531 | Finalización de Conjunto WIP | 16/12/2009
4420 | ALUMINA PS-M BB720 | 47.760,000 | KG | ,44737 | 21.366,39 | 04 | 10 | 8100110 | SOBRANTES | 31/03/2010
4420 | ALUMINA PS-M BB720 | 97.680,000 | KG | ,44737 | 43.699,10 | 04 | 10 | 8201603 | SOBRANTES | 30/04/2010

Considerations:

The value that you see in Transaction id is the max value that the field have; is that to say, from each type of transactions it shows the highest (last) transaction Id. Looking at the example, the problem now is that we want to narrrow the result a llitle bit more. We want that the report shows only from each product name either the highest transaction id or the highest movement date (that in the example above is the transaction id 8201603 which have the highest movement date –30/04/2010–).

I am stopped at this point cause I do not see how to filtering this data to achieve the result that we want.

Any suggestion or help would be appreciated, cause sincerely, I just do not see how to do it.

Thanks in advance.

Luis.