0

Getting Month Start Date from Month Year

I have a dashboard with a presentation variable of Month Year called my_pv and want to filter by Month Start Date based on the selected Month Year.

I have explored the use of session variables but couldn't get there with that approach since I cannot reference a presentation variable within a session variable.

I have tried using lookup(), but it only returns an error.

9replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Assume that the presentation variable with the value 'Jun-20'.   What we can do is to use a formula filter, which can be an applied filter at the dashboard level or an insight filter.

    Schema.Table.PeriodStartDate=parseDate($PeriodName,'MMM-yy')

    Like
  • If it does not work for you, please try this :

    parseDate(concat('01-', $OpenPeriod),'dd-MMM-yy')

    In a few old releases, there was a bug in the parseDate function.

    Like
    • Dylan Wan, I understand this method but not the first one you suggest. How does the parseDate() get the start of the month? I will play with both. 

      That said, I was hoping for a way to reference the date table as I have a business date table and really need to get to the first business day of the month.

      Thanks.

      Like
    • Marc Paige Here is what I can think of.

      In the business date table, I guess that you will have one record per date and one of the column will be the period name (Month Year).
      - Create an Incorta table on this business date table to group the data by period name and get the min Date.
      - This PeriodTable table will have one row period period and will be the table for lookup.  Define the period name as the key for this table.
      - Where you need to get the start date, you can use
      LOOKUP(Schema.PeriodTable.PeriodStartDate, Schema.PeriodTable.PeriodName, $PeriodName)

      Hope this helps.

      Like
    • Dylan , I have my business dates table joined as a parent to the Date table. I think I should be able to build the Incorta table using the Month Year from the child side as the group by and the min business date from the parent. I will give this a try. Thanks!

      Like
    •  Dylan  , this worked well. I included both ends; start and end business dates. Thanks again.

      Like 1
  • Marc,

    I am sure you must have figured it by now. Posting these videos for the folks who may need to do something similar:

    Filter by first business day of the month - part 1

    This video shows how to filter by date = first business day of the month (does not use lookup function)

    https://youtu.be/rI9Y3Zh1ZgU

     

    Filter by first business day of the month - part 2

    This video shows how to filter by date >= first business day of the month (use lookup function)

    https://youtu.be/XE0rW4Suv94

    Like 1
    • Anurag Malik Do you have any special formula columns for these videos? It seems like there is at least one but it is not shown. Please elaborate.

      Like
    • Marc Paige I do not have any  special formula columns. For second video I created business day table (parent of date table) and used lookup function as shown in the video to get date for first business day of the month.

      Like 1
Like Follow
  • Status Answered
  • 5 mths agoLast active
  • 9Replies
  • 29Views
  • 3 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!