Removal of the negative values which came as difference between two dates in average calculation per month using SAP Bex Query Designer

[former_member209719]

This document gives a clear picture on how to remove the negative values which came as difference between two dates in average calculation using SAP Bex Query Designer.

First we are calculating the difference between dates in the run time and if the difference between the dates is +ve then we are considering them in
average calculation.

Business scenario:

While displaying the average of difference between PO Creation Date – PR Release Date based on PO Creation Month, business does not want to consider those records when the difference between these two dates are –ve. It means we need to consider those PO and PR’s only when PO Creation Date is after PR Release Date.

steps:

1) We need to create Formula with replacement path on PR Approval Date and PO Creation Date

2) We have to create formula between PO Creation Date and PR Approval Date with execeptional aggregation of  Average based on PO line Item which is the least granularity

  

3) Using above formula, PR Approval Date and PO creation Date we need to create below fomula with execeptional aggregation of  Average taking reference characteristic as PO line Item which is the least granularity. Here we are considering only those records in which PO Cretion Date is greater than PR Approval Date

4)  Using above formula we need to create one formula with execeptional aggregation of  Summation taking refrecne characteritic as PO line Item which is the least granularity

5)  We need to create formula which gives the PO Line item count for those entries which are having PO Creation Date greater than PR Approval Date

6) We need to create formula using PO Creation Date and PR Approval date summation which got in step 4 divided with the PO Line Item count which we got in step 5

Apart above formula all the remaining formulas which created upto point 6 are hidden and we are going to display only the formula in point 6 in the query out put.

7) Example for better understanding

7500204176, 7500202355, 7500201406 PO numbers are taken as example among these three PO 7500204176 and PR 11235821 are having difference between PO Creation Date and PR Approval Date as –1 but it is showing 0 in the report output. This record should not be considered in average calculation when we remove all the display characteristics from ROWs .

As per the output below the above record i.e. PO 7500204176 and PR 11235821 which has actually negative value in difference between PO Creation Date and PR Approval Date is not considered and showing only 2 as average.

Note : Even though Negative values which came as difference between PO Creation Date and PR Approval date will not be considered
in average calculation but they will be visible and show the difference between them as 0 when we drill down both PO, PR Document numbers and Line items in to the report output.

This document was generated from the following discussion: The specified item was not found.