If we call this presentation variable pDate, for prompted date, our filter now looks like this:Ī best practice is to use default values with your presentation variables so you can run the queries you are working on from within your analysis. To make this even better, instead of using CURRENT_DATE you could use a prompted value with the use of a Presentation Variable (for more on Presentation Variables, click here). (For a list of other similar functions like DAYOFYEAR, WEEKOFYEAR etc. Thus, we would subtract 18 days from CURRENT_DATE, which is December 18 th, and add 1, giving us December 1 st. This is useful because we can subtract this amount to give us the first of the month from any date by simply subtracting it from that date and adding 1.įor example if today is December 18 th, DAYOFMONTH(CURRENT_DATE) would equal 18. This function will return the calendar day of any date. In this scenario, we can use the DAYOFMONTH() function.

Note: it is always a good practice to include a second filter giving an upper limit like 'Time'.'Date' = the first of the month. So in the end we have created a functional filter making Date >= 1 week ago, using a TIMESTAMP that subtracts 7 days from today. The third argument defines the starting point in time, in this example, the current date.The second argument determines how many of that interval we will be moving, in this case -7 days.This means that we will be working with days. The first argument, SQL_TSI_DAY, defines the Time Stamp Interval (TSI).Here we are saying we want to include all dates greater than or equal to 7 days ago, or from the current date. To create robust TIMESTAMP functions you will first need to understand how the TIMESTAMP works. Often users want to know what a report looked like at some previous point in time, to provide that level of flexibility TIMESTAMPS can be used in conjunction with Presentation Variables. If the goal of your report is to display Month-to-Date, Year-to-Date, rolling month or truly any non-static period in time, the TIMESTAMP function will allow you to get there. The TIMESTAMP is an invaluable function that allows a user to define report limits based on a moving target. This entire document will only use values from a date and revenue field. In this example we will create a report that displays a year over year analysis for any rolling number of periods, by week or month, from any date in time, all determined by the user. I intend to transform you into an expert with these functions and by the end of this page you will certainly be able to impress your peers and managers, you may even impress Angus MacGyver. TIMESTAMPS and Presentation Variables can be some of the most useful tools a report creator can use to invent robust, repeatable reports while maximizing user flexibility.

Fnaf 4 ambience