Hi All,

This was my actual requirement.

Requirement:

We have analysis which uses Time series functions AGO to calculate quarter revenue data. But using ago it generated multiple queries so the execution speed is very slow.

So if a user enters 'FYQ3-2016' then the Analysis will by itself get data for the quarters 'FYQ3-2016','FYQ2-2016','FYQ1-2016' and 'FYQ4-2015'

Solution:

To tune this we are trying to use Oracle LAG function to get earlier quarter data.

Steps Taken:

Step 1: View in DB

We've created a view in oracle database on a physical table which contains the following columns

CURRENT_QUARTER, QUARTER_AGO_1, QUARTER_AGO_2 and QUARTER_AGO_3

So it will contains data in this format by itself.

Data Sample:

Step 2: Analysis on view

We have created an analysis called "earlier_quarters" on this view on which we've applied a filter on the field 'CURRENT_QUARTER'

Result of this analysis is like below

Step 3: Create another analysis based on the created analysis 'earlier_quarters'.

The new analysis is based on the result of the earlier_quarters analysis.

Below we can see the highlighted section where the earlier analysis is used.

Now we are expecting the quarter results to contains all 4 quarters, as shown in Step 2 the result of the "earlier_quarters" analysis.

Yet we are only getting back results for 2 quarters. And 2 of the quarters 'FYQ2-2016' and 'FYQ1-2016' are missing.

Help Required:

Now if we hard-code the 'FYQ2-2016' and 'FYQ1-2016' in the new analysis we are getting results for those, so there is data for these quarters.

Can anyone suggest or help us understand why this issue is occurring?

Thanks and Regards,

Pratyush