Dynamic Trend Calculation for KPI in Analytical CDS View

[tobias_grimm_ksg]

I use an Analytical CDS View (Query) to get the Average Delivery Delay of Sales Order Items as an KPI in my SAPUI5 application. The value is calculated in the CDS view:

@AnalyticsDetails.query.decimals: 3
@AnalyticsDetails.query.formula: 'NDIV0( $projection.TotDelivToReqdDelivDelayInDays / $projection.NumberOfIncomingSlsOrderItems )'
cast(1 as avg_deliv_to_reqd_deliv_delay) as AvgDelivToReqdDelivDelayInDays

I created a DataPoint for this field: 

<Annotation Term="UI.DataPoint" Qualifier="DataPointDurchschnittLieferverzug">
  <Record Type="UI.DataPointType">
    <PropertyValue Property="Title" String="someTitle" />
    <PropertyValue Property="Description" String="someDesc" />
    <PropertyValue Property="Value" Path="AvgDelivToCmtdDelivDelayInDays" />
    ...
    <PropertyValue Property="TrendCalculation">
      <Record Type="UI.TrendCalculationType">
        <PropertyValue Property="ReferenceValue" Path="ReferenceField" />
        <PropertyValue Property="UpDifference" Decimal="3" />
        <PropertyValue Property="StrongUpDifference" Decimal="10" />
        <PropertyValue Property="DownDifference" Decimal="-3" />
        <PropertyValue Property="StrongDownDifference" Decimal="-10" />
      </Record>
    </PropertyValue>
  </Record>
</Annotation>

I would like to show a trend indicator for my KPI. Lets say, I want to use the average value of the past 7 days as a reference and get a trend (up/down) from that. I know its possible to get a reference value from another field or set a static value for the ReferenceValue property.

My question is, is it possible to calculate the reference value in the query view based on a time span? 
All tutorials regarding trend calculation of KPIs I found online always assume you have a reference value already present as a field in your view. But in this case, the KPI value that I need a reference for is calculated in the same view...

Would it be possible to get the reference value from a different view instead of a field of the same entity? 

[tobias_grimm_ksg · Accepted solution]

Due to the lack of a more elegant solution, I now solved this problem by creating my own cds views for calculating a trend reference value.

I use the following steps:

1. Select all fields that are relevant for trend calculation in the Fact View 
2. Use a Table Function to only include rows within the last X days via SQL Script
3. Aggregate all rows into a single line using SUM( ) functions
4. Calculate the average values (e.g. "NrOfDelivAsCommitedItems / TotalNrOfItems")  
5. Cross Join with the original data, so that each row includes the average value in the Cube View

It is not an elegant solution, but it allows me to use the dynamically calculated reference value in the KPI:

[MKreitlein · Accepted solution]

Hello Tobias... I read your question right now ... and saw your solution. I would also have proposed to try calculating whatever you need in an underlying view and then create the Formula in your Analytical view. So, I think this is the best solution apporach. BR, Martin