0

Next Month Start Date

I have tried many different ways to provide this value. My first thought was a session variable, but I cannot see a way to make that work with the query() functions. I tried augmenting the date table with a formula column, but it is empty. This is the formula:

date(concat(string(if($currentMonth = 12, $currentYear+1,$currentYear)),"-",if($currentMonth=12,"1",string($currentMonth)),"-","01"))

I am looking for an equivalent to the $currentMonthStart to be used in dashboard filtering.

11replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Mark,

    If you use date csv that we ship with Incorta, it will be easier to do this.

    Steps:

    1. Create date table based on date csv file

    2. Join this date table with your transaction table

    3. Use following function to get Next Month Start Date. This function assumes that you have created a date schema with date table.

    • addDays(date.date.Month_End_date,1)

     

    If you just need to get Next Month Start Date after current month, in that case use following expression in a session variable:

    • query(addDays(date.date.Month_End_date,1),date.date.Date=$currentDate)
    Reply Like
    • Anurag Malik Your last line is exactly what I was hoping for. Thank you!

      Reply Like
    • Marc Paige Next roadblock: how do I use the session variable in a date filter? The default value selection is a calendar control with the built in variables.

      I successfully built and tested the session variable.

        I tried to use inQuery for the filter and get invalid date. I used this query:

      queryDistinct(Audit.date.Date,Audit.date.Date=$nextMonthStartDate)

      Resulting in:

      Reply Like
  • Hi Marc,

    Forgot to mention that session variable will return string data type.

    You will have to convert  session variable into date data type.

    Define you session variable as:

    • query(formatDate(addDays(date.date.Month_End_date,1),"yyyy-MM-dd") , date.date.Date=$currentDate)

    If session var name is $NextMonthStartDate, use it as date($NextMonthStartDate) in another expression.

    Reply Like
    • Anurag Malik Thanks Anurag. I am still stuck at the filter part. When using inQuery, no matter what I do, I get "Invalid Date".  The goal is to filter rows that have a date >= to the $nextMonthStartDate.

      Reply Like
  • Hi Marc,

    You cannot use inQuery for this. It's designed for different use case.

    Use formula in the insight to filter data:

    Example:

    date.date.Date>=date($nextMonthStartDate)

    Reply Like
    • Anurag Malik Thanks again. I understand what I need to do.

      Reply Like
  • To round out the Q&A, here is a summary of what I ended up with:

    Session Variables called nextMonthStartDate and nextMonthEndDate. These are used for filtering dated items for projecting into the next, and only the next, month. The queries are:

    nextMonthStartDate: query(formatDate(addDays(Audit.date.Month_End_date,1),"yyyy-MM-dd"),Audit.date.Date=$currentDate)

    nextMonthEndDate: query(Audit.date.Month_End_date,Audit.date.Date=$nextMonthStartDate)

    For filtering, I use the and() function to test for the boundaries (simulating the between date operator). For example:

    and(schema.table.datefield>=date($nextMonthStartDate),schema.table.datefield<=date($nextMonthEndDate))

    Reply Like
    • The and() cannot work as a between function directly. Still working out that logic. Will post once solved.

      Reply Like
  • Correction to nextMonthEndDate formula: 

    query(formatDate(Audit.date.Month_End_date, "yyyy-MM-dd"),Audit.date.Date=date($nextMonthStartDate))

    This was the source of the and() not working. All is well now.

    Reply Like 2
Like Follow
  • Status Answered
  • 6 mths agoLast active
  • 11Replies
  • 178Views
  • 3 Following