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.
If you use date csv that we ship with Incorta, it will be easier to do this.
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.
If you just need to get Next Month Start Date after current month, in that case use following expression in a session variable:
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.
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:
For filtering, I use the and() function to test for the boundaries (simulating the between date operator). For example: