Percentage based on the Date and Dimension - WEBI

66 Views

Hello,

I have to calculate percentage based on the Dates and a Dimension.

I have BEX Query as source and reporting is done on BOXI - WEBI

Here is the table structure in the report.

List# , Process01, Process02.

List# is unique and Process01-Has 4 operations, Process02 has 3 operations (and these Operations can change from 3 to 7 or 2).

Operations will have a 'Date' only when it is completed.

and when all the operations in a 'Process' have 'Date' then i need to get the 'Latest Date'. If few of the operations are not confirmed, then i need to get the percentage like when 2 of 4 operations have date it will be 50% and when no operation is confirmed then 0% and so on .

Example Cube data:

List#,ProcessCategory,Operation,Date

100,Process01,456,11/11/2010.
100,Process01,457,11/12/2010.
100,Process01,489,
100,Process01,490,
100,Process02,890,11/13/2010.
100,Process02,891,11/14/2010.
100,Proceess02,892,11/15/2010

Result:

List#, Process01, Process02

100,50%,11/15/2010.

Please help me in this, i tryed different logics but it is giving ERROR at percentage.

Your help would be appreciated!

Thanks in advance.