Last Month Start ($lastmonthstart) Exists but not Last Month End ($lastmonthend)

[nikhil_cr]

Hi, 

I'm trying to set a Default value for the Dashboard Filter (in Prompt Filter) which is a date filter as " $lastmonthend" but this variable does not exists. 

I can see $lastmonthstart variable but not $lastmonthend. How can I create this variable and use it in my dashboard? So that Dashboard filter (Date Column) is always set at Last Month End date ?   

[Anonymous]

Hi,

you should use formula below

[nikhil_cr]

Hi @Anonymous  Thanks for your reply. Using this formula in the dashboard filter will restrict data till last month end date. I want to keep the default values for the date column as last month end date but also give the users the flexibility to change this date. Making it dynamic. 

FYI - I tired creating a internal variable using this formula and use it in the dashboard. But the variable gives me an error - "Invalid Date"

[nikhil_cr · Accepted solution]

I got this, seems like session variable created are strings by default. We have to convert the session variable to desired format. In this case data type should be - Date Type.

I used the below query to create a session variable and it worked.  

queryDistinct(formatDate( date_Column, "yyyy-MM-dd" ), date_Column = addDays( $currentMonthStart, -1 ) ))