Date Picker

Date picker is similar to the Textbox control and contains a calendar helper to pick the date  quickly.
 
How to add Date Picker
 
 
Just drag the "Date Picker" and drop in the canvas.
 
NOTE: You can directly type the date or pick from the popup wizard
 
How to change the title
 
Double click the widget
 
Change the header text to any name you want.
 
How to change the id/name
 
Every html control passes information to the entire dashboard using a specific channel. And all the data widgets would selectively listen to these channels for any change in values. So when the user selects a different value in the control, this new value is broadcasted to this channel and every widget that is subscribed to this channel is notified of the change. The widget will then update its data based on the new channel value.
 
The name of the channel is defined using the widgets name property.
 
1. Right click on the Widget
2. Select 'Edit Properties'
3. Scroll up to the top and find "name"
4. Change the value for the name property and give a descriptive name
 
 
 
Edit properties
 
Change the name
 
 
In the name, we just appended with a description "start_date" to indicate that this control is for start date. You can change the entire name but in this case we just chose to append it and keep the other information as it is.
 
 
How to define a date range
 
Usually dates work in pair of begin and end date. On dashboards and reports it is always useful to provide date ranges so users can specify both the start and end dates.
 
We can simply drag a new date and call it an end date, or right click on the current date and select "Duplicate"
 
 
We next rename the title to end date.
 
And we need to also change the channel ID/name for the end date
 
 
 
How to use the Date inside data widgets
 
As seen, whatever you type or select the date, the value is passed as a string to the data widget.
 
So within the SQL, we will need to format or convert to appropriate date value depending on the type of database
 
We add a grid and change the SQL to show the full name and hire date
 
 
In Excel, you can use the format function on the date for specific formatting.
 
Now we will try to attach the start and end date to the hire date
 
 
We change the SQL to include a condition on the hire date as below
 
 
select "full name"
,format("hire date",'dd-mmm-yyyy') as hiring_date
from [emp_data$]
where "Hire Date"
>= format(param<html_datebox_d17_start_date>,'mm/dd/yyyy')
and "Hire Date"  <= format(param<html_datebox_d18_end_date>,'mm/dd/yyyy')
 
 
 
So here are some variations you may use for different types of database  for the format of mm/dd/yyyy
 
Microsoft Excel
format(param<channel_name>,'mm/dd/yyyy')
 
Microsoft Access
format(param<channel_name>,'mm/dd/yyyy')
 
Oracle
to_date(param<channel_name>,'mm/dd/yyyy')
 
SQL Server
convert(datetime ,param<channel_name> , 101)
 
MySQL
str_to_date(param<channel_name>,'%c/%e/%Y')
 
 
How to set default value
 
You can set a default value for each control.
 
1. Right click on the widget
2. Select "Edit Properties"
3. Go to the top and there is a property "default_value"
4. Set a value
 
 
Date picker has an extremely powerful functionality of providing default values. You can provide default values in variety of formats. Typically this is useful for setting the start and end dates to a certain date boundaries when the dashboard launches. The date boundaries are first day of month, last day of month, first day of week, quarter, year etc
 
The default value for date picker is specified within square brackets as below.
 
 
Allowed values for Date boundary (D1)
  • fdm : first day of month
  • ldm : last day of month
  • fdw : first day of week
  • ldw : last day of week
  • fdy : first day of year
  • ldy : last day of year
  • fdq : first day of quarter
  • ldq : last day of quarter
  • today : today's current date
 
Allowed values for Modifier Unit (D3)
  • d = day
  • m = month
 
Allowed values for Modifier Qty (D2)
  • Any positive or negative integer
 
 
Assuming today is 8-Apr-2012 (this page was documented on this date)
 
Dates without modifiers (Note: if D2=0 then D3 is ignored)
 
1. Get first day of current month (note: D2=0)
D1=fdm , D2=0 , D3=d 
["fdm" , "0" , "d"] == "2012-04-01"
 
 
2. Get last day of current month (note:  D2=0)
D1=ldm , D2=0 , D3=d
["ldm" , "0" , "d"] == "2012-04-30"
 
3. Get first day of current year (note:  D2=0)
D1=fdy , D2=0 , D3=d
["fdy" , "0" , "d"] == "2012-01-01"
 
4. Get first day of current year (note:  D2=0)
D1=ldy , D2=0 , D3=d
["ldy" , "0" , "d"] == "2012-12-31"
 
5. Get first day of current quarter (note:  D2=0)
D1=fdq , D2=0 , D3=d
["fdq" , "0" , "d"] == "2012-04-01"
 
6. Get last day of current quarter (note:  D2=0)
D1=ldq , D2=0 , D3=d
["ldq" , "0" , "d"] == "2012-06-30"
 
7. Get first day of current week (note:  D2=0)
D1=fdw , D2=0 , D3=d
["fdw" , "0" , "d"] == "2012-04-08"
 
8. Get last day of current week (note:  D2=0)
D1=ldw , D2=0 , D3=d
["ldw" , "0" , "d"] == "2012-04-14"
 
9. Get today's date (note:  D2=0)
D1=today , D2=0 , D3=m
["today" , "0" , "m"] == "2012-04-08"
 
 
Examples with Day manipulation
 
10. Get first day of current month and add 3 days  (note:  D2=3 and D3 = d, signifying add 3 days )
D1=fdm , D2=3 , D3=d
["fdm" , "3" , "d"] == "2012-04-04"
 
11. Get last day of current month and subtract 3 days  (note:  D2=-3 and D3 = d, signifying subtract 3 days )
D1=ldm , D2=-3 , D3=d
["ldm" , "-3" , "d"] == "2012-04-27"
 
12. Get first day of current year and subtract 5 days  (note:  D2=-5 and D3 = d, signifying subtract 5 days )
D1=fdy , D2=-5 , D3=d
["fdy" , "-5" , "d"] == "2011-12-27"
 
13. Get last day of current year and add 5 days  (note:  D2=5 and D3 = d, signifying add 5 days )
D1=ldy , D2=5 , D3=d
["ldy" , "5" , "d"] == "2013-01-05"
 
14. Get first day of current quarter and add 5 days  (note:  D2=5 and D3 = d, signifying add 5 days )
D1=fdq , D2=5 , D3=d
["fdq" , "5" , "d"] == "2012-04-06"
 
15. Get last day of current quarter and minus 5 days  (note:  D2=-5 and D3 = d, signifying minus 5 days )
D1=ldq , D2=-5 , D3=d
["ldq" , "-5" , "d"] == "2012-06-25"
 
16. Get first day of current week and minus 3 days  (note:  D2=-3 and D3 = d, signifying minus 3 days )
D1=fdw , D2=-3 , D3=d
["fdw" , "-3" , "d"] == "2012-04-05"
 
17. Get last day of current week and add 5 days  (note:  D2=5 and D3 = d, signifying add 5 days )
D1=ldw , D2=5 , D3=d
["ldw" , "5" , "d"] == "2012-04-19"
 
18. Get today's date and go backwards 5 days  (note:  D2=-5 and D3 = d, signifying minus 5 days )
D1=today , D2=-5 , D3=d
["today" , "-5" , "d"] == "2012-04-03"
 
Examples with Month manipulation
 
19. Get first day of current month and add 3 months ie go forward 3 months  (note:  D2=3 and D3 = m, signifying add 3 months )
D1=fdm , D2=3 , D3=m
["fdm" , "3" , "m"] == "2012-07-01"
 
20. Get last day of current month and go backward 3 months  (note:  D2=-3 and D3 = m, signifying subtract 3 months )
D1=ldm , D2=-3 , D3=m
["ldm" , "-3" , "m"] == "2012-01-31"
 
21. Get first day of current year and minus 5 months  (note:  D2=-5 and D3 = m, signifying minus 5 months )
D1=fdy , D2=-5 , D3=m
["fdy" , "-5" , "m"] == "2011-08-01"
 
22. Get last day of current year and add 5 months  (note:  D2=5 and D3 = m, signifying add 5 months )
D1=ldy , D2=5 , D3=m
["ldy" , "5" , "m"] == "2013-05-31"
 
23. Get first day of current quarter and add 5 months  (note:  D2=5 and D3 = m, signifying add 5 months )
D1=fdq , D2=5 , D3=m
["fdq" , "5" , "m"] == "2012-09-01"
 
24. Get last day of current quarter and minus 5 months  (note:  D2=-5 and D3 = m, signifying minus 5 months )
D1=ldq , D2=-5 , D3=m
["ldq" , "-5" , "m"] == "2012-01-30"
 
25. Get first day of current week and minus 3 months  (note:  D2=-3 and D3 = m, signifying minus 3 months )
D1=fdw , D2=-3 , D3=m
["fdw" , "-3" , "m"] == "2012-01-08"
 
26. Get last day of current week and minus 5 months  (note:  D2=-5 and D3 = m, signifying minus 5 months )
D1=ldw , D2=5 , D3=m
["ldw" , "5" , "m"] == "2012-09-14"
 
27. Get today's date and minus 5 months  (note:  D2=-5 and D3 = m, signifying minus 5 months )
D1=today , D2=-5 , D3=m
["today" , "-5" , "m"] == "2011-11-08"
 
 
 
 
Value Prefix and Postfix
 
In the properties, at the very bottom there are two important fields
 
These are valuePrefix and valuePostfix
 
By default they are set to a single quotation [ ' ] value. In case if you want to pass numeric values or some other value like double quotes etc then you can set the value to empy like
"valuePrefix":"" to pass no quotes around the value. Same for valuePostfix
Use online Web Dashboards in the cloud or download and host your own Dashboards