5

How to: Create a dynamic set of dates

During a recent discussion we got asked how to create a dynamic set of dates.

There are two approaches to the tasks, in this video Moshe Yossef takes you through both ways and explains how to achieve the task.

Solution:

Scenario 1: 

Provide a list of the last 10 Days, whether they have data or not, assuming and the date column's last date today.

Scenario 2:

Provide a list of the last 10 Days, even if the date column has dates later than today.

And here is the video for you to review

How to create a dynamic set of dates

You can find the example in our Explore Pyramid Server for Review in the Public Content, Knowledge Base Folder.

 

If you have questions or suggestions, please leave them below the Article.

5 replies

null
    • Ying_Dong
    • 2 mths ago
    • Reported - view

    Thanks , this is really helpful. Just further this method, can we change today or max date to any date, like the user can pick any date from date slicer, and provide the last 10 days data? or if the user pick the date range, i want to show all date like the following:

    Referral volume
    = count (distinct if (
    FIRST_REFERRAL_DATE >= (minDate -365) and
    FIRST_REFERRAL_DATE < maxDate,
    REFERRAL_ID))

      • Customer Solutions Architect
      • Moshe_Yossef
      • 2 mths ago
      • Reported - view

       

      the first thing can be done just by using formula in a calendar filter.

      The second is more complicated. Just to be sure - you want a user to select a range, and receive all the dates from a year before the first date in the range, and the last date in the range?

      • Ying_Dong
      • 2 mths ago
      • Reported - view

      I want it the first date in the range.

      • Customer Solutions Architect
      • Moshe_Yossef
      • 2 mths ago
      • Reported - view

       

      I don't understand then.
      Can you explain a little more - you have a table with "referrals" that have "first_referral_date"
      you want to choose MinDate and MaxDate, and receive:
      All "first_referral_date" starting with MInDate - 365 and ending with MaxDate?
      and then you want a distinct count of the "referrals" on this range?

      • Ying_Dong
      • 2 mths ago
      • Reported - view

       

      Yes, there have first_referral_date in referrals table.

      For example, the user pick date range Oct 1, 2024- Oct 8, 2024, my goal is count the referral numbers which first_referral_date between Oct 1, 2023 - Oct 8, 2024.

Content aside

  • 5 Likes
  • 2 mths agoLast active
  • 5Replies
  • 304Views
  • 2 Following