0

How to create date ranges and aggregate them?

Is it possible to create a date range with Start and End dates?  If so, can we aggregate the range to show the values as Average or Sum?

2 replies

null
    • Jason_Picker
    • 4 yrs ago
    • Reported - view

    Yes, you can build simple date ranges using model parameters.  The easiest way to do it is to create the parameters in the Discover module and assemble them in the Formulate module.  For example, let's say you want to create a date range based on a Day.  You could find the Day attribute in your model and right-click on it and choose Create Parameter.

     

    In order to save and reuse this parameter, you can then right-click on the newly created parameter and choose the Make Shareable menu.

     

    You can then make a second parameter using the same two steps.  Once those parameters are created, you want to create a Range List from the Formulate module.  You select the Start parameter as the first member and the End parameter as the second parameter.

     Save the list and use it in your reports.  See the example below.

      

    If you want to create aggregate values of the data range, you have two options: simple aggregates in Discover and aggregates in Formulate.  To create simple aggregates in Discover, just right-click on the date range list and choose Create Formula > Aggregate Functions > Aggregate/Sum/Average/etc.

     

    This method adds the aggregates to the same attribute the range belongs to.  This is fine if you plan on using the aggregates in tables or want the date range to be behind the scenes (i.e. not on rows or columns).  However, if you want to create aggregates that can be used as reference lines on a trend chart, for example, you will want to create them in the Formulate module and place them on the Measures dimension.

     Hopefully this helps.

    • Jason_Picker
    • 4 yrs ago
    • Reported - view

    Here are a couple YouTube videos to show this in action.

Content aside

  • Status Answered
  • 4 yrs agoLast active
  • 2Replies
  • 78Views
  • 1 Following