1

Creating a dynamic parameter for a date range

Hi Everyone, 

I am trying to figure out a solution for the following issue. 

We have a contract with a start date and and a end date dimension. We want to select the active contracts within a year. 

We want to create a dynamic parameter were the ship date (year) is the input. This input should dynamically select the active contracts in that year by selecting the correct values of the start and end date reflecting an active contract.

We are able to set the filters up in a discovery as seen below but these need to be set manual, we want this to become dynamically.

Example of logic see below, the YYYY is dynamic and should change base on the input of ship date year

input ship date (Year) = YYYY

Then

Contract start date list of all values before 01-01-YYYY

Contract end date list of all values after 01-01-YYYY

 

It would be very helpful if anyone knows how to resolve this question, thanks in advance!

3 replies

null
    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    Hi IT Support,

    Your logic is that from the given year, you want all dates before that for the start date and all dates after that for the end date, yet your example screenshot shows the last date for start date to include 2021-01-01 when the year is 2021.

    IT Support said:
    input ship date (Year) = YYYY
    Then
    Contract start date list of all values before 01-01-YYYY
    Contract end date list of all values after 01-01-YYYY

    Based on your defined logic, should not the last start  date be 2020-12-31 when 2021 is selected? And the first end date should be 2021-01-02?

    Can you clarify this?

    Thanks,

    Ian

    • IT_Support
    • 1 yr ago
    • Reported - view

    Hi Ian, 

     

    Thank you for the feedback. 

     

    Let me clarify this using the following example: There is a contract from 01-01-2021 until 31-12-2022

    This has a contract start date of 01-01-2021  and a contract end date of 31-12-2022

    For 2020 we don't want a count because it is not active

    for 2021 we do want a count because its is active

    for 2022 we do want a count because its is active

    for 2023 we don't want a count because it is not active

     

    Selected year: 2021 (YYYY)

    So parameters should be:

    Contract Startdate: equal to or before 01-01-YYYY

    Contract Enddate: equal to or after 01-01-YYYY ( because a contract might be shorter then a year and we want to capture all contracts in a year)

    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    Thanks for the clarification, very helpful.

    OK, this is an interesting problem, as you would like a single filter on year to apply to two sets of Ranges, but those Ranges are on different hierarchies, namely Start Date and End Date. We cannot therefore simply take a single hierarchy like Start Date Year as the filter, as it would not interact correctly with the End Date hierarchy, we'd need the End Date Year to do that. So the first problem is how to provide a single Filter which will input into both End Date Year and Start Date Year. Then we'll need to construct two Range sets, one for Start Date and one for End Date. To do that, we'll need to identify the start and end dates of the ranges, based on the year value supplied.

    Create a Global Parameter that will consist of a set of values in a Year type format. A Global Parameter is not tied to any one hierarchy and is simply a list of arbitrary values:

    Create a dynamic list that is defined by the start and end dates for Start Date. To do that, we'll need to construct the Start Date Year from the Global Parameter an use that to find the Opening Period of Start Date based on the Start Date Year, which if the year is 2023, will be 2023-01-01, i.e. the end point of the range for Start Date.

    So first is to create the Start Date Year from the Global Parameter. We can use the function StrToMember() to map the Global Parameter to the start Date Year. We'll have to drop down into Script mode for a custom list to achieve this:

    StrToMember([data].[Start Date year], [global].#[f80ac19c-991d-4a76-b7c9-8a6a72701f11])

    Now we need to identify the opening period (I.e. first date) that exists for the Start Date Year 2023:

    OpeningPeriod([data].[Start Date],StrToMember([data].[Start Date year], [global].#[f80ac19c-991d-4a76-b7c9-8a6a72701f11]))

    This will be the end point of the Start Dates Range.

    We need to find the start point for the range which will be the first element of the Start Dates Hierarchy which we can do using the FirstEelement() Function:

    FirstElement([data].[Start Date])

    Now we need to put then together as the start and end point of our Range function to produce the set of Start Dates, from inception of the model, to the first day of the selected year:

    Range(FirstElement([data].[Start Date]),OpeningPeriod([data].[Start Date],StrToMember([data].[Start Date year],[global].#[f80ac19c-991d-4a76-b7c9-8a6a72701f11])))

    So now we have our Start Date set of dates.

    The construction of the End Dates set of dates follows a similar approach, but this time we need to use the Global Year parameter to construct the End Date Member that will be used to find the starting point for the range of End Dates. Here's the completed expression, I'll leave it for you to decompose the elements.

    Range(OpeningPeriod([data].[End Date],StrToMember([data].[End Date year],[global].#[f80ac19c-991d-4a76-b7c9-8a6a72701f11])),LastElement([data].[End date]))

    Now we have the sets of Dates we need, driven by  a single global parameter. To apply them to any Discover View, select the Range in each hierarchy and drag it to the Filter drop zone. Select "Combined Elements" to show content where the Start or end date is included in any of the values  in the respective ranges:

    We now have our final analysis. Selecting global Year 2020, will show all Transactions which have a Start Date on or before 2020-01-01, and an End Date of on or after 2020-01-01:

    Hope that helps!

    Ian

Content aside

  • Status Answered
  • 1 Likes
  • 1 yr agoLast active
  • 3Replies
  • 99Views
  • 2 Following