Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Point in Time comparisons with future dates

Hi All,

In general, we restrict the Calendar to Maximum date of the fact data.

I have a requirement to calculate YTD, MTD, Prev YTD, MTD dynamically the following way which will have future dates as well:

1) Calendar data is coming from the source DB and it will have dates in future. Eg: If current date is 25-Jul-2016, Calendar can have 31-12-2017 as Max Date.

   Future dates are needed for budgeting purpose

2) Fact will always have the history data.

3) The YTD, MTD should be defaulted to the most recent fact data when an App is opened ( i.e. when no selection is made) and Prev YTD and Prev should be offset accordingly.

4) If the user makes any selection ( be it a date of the past or of the future), the values should be calculated for that period till the Maximum of the selected date.

Eg: If the user selects 21-05-2015 and 25-06-2015, 

  then YTD should be calculated from 1-Jan-2015 to 25-06-2015.

  MTD should be  from 1-Jun-2015 to 25-06-2015.

5) Similarily, the user makes a selection in future, then the behaviour should be as follows:

  If the user selects 21-05-2017,

  then YTD will be 0 since there are no measures/facts, Prev YTD should be calculated from 1-01-2016 to 21-05-2016

  MTD will be 0, Prev MTD should be calculated from 1-05-2015 to 21-05-2015

Any suggestions to implement this requirement?


1 Reply

Use an As-Of table that includes future dates. See this document for an example of the As-Of table concept: Calculating rolling n-period totals, averages or other aggregations

talk is cheap, supply exceeds demand