This requires Admin or Data Admin privileges for implementation.
Please Note: If you do not have the correct privileges to access Data Hub you will receive the following upon your login attempt. If you get this message and think it is an error please see your organization's iDashboards Administrator for assistance as Support is unable to give you access.
Convert Month to number or Number to Month
Regex
Finds the numbers after -
(\d+)[^-]*$
Add or Remove Days from Date
This expression will add or subtract days based on the number used in the variable days. The output can be string or datetime format.
Data Hub Formatted
Input example: Datatype: 1901-01-01
Output Example: Datatype: Datetime - 1901-04-01
var d = new Date($val["mydate"]);
var days = 90;
d.setDate(d.getDate() + days);
var day = d.getDate();
var month = d.getMonth() +1;
var year = d.getFullYear();
year+'-'+month+'-'+day
Date to Year
Data Hub Formatted
Input example: Datatype: Date - 1900-01-01
Output Example: Datatype: Number - 1900
Date to Year output in Data Hub - this example will convert your variable from a long date to the four number year.
Important you will notice that there is an IF statement, the IF statement is to filter out any null or blank data values that could cause the script to error out on execute.
if($val["longdate"] != null || $val["longdate"] != ''){
$val["longdate"].getYear()+1900}
else{0}
This expression can also be used to convert an entire column of data, by using something similar to the following:
($val["ColumnName"]).getYear() + 1900;
Date to Month
Data Hub Formatted
Input example: Datatype: Date - 1900-01-01
Output Example: Datatype: Number - 01
Date to Month output in Data Hub - this example will convert your variable from a long date to the four number month.
Important you will notice that there is an IF statement, the IF statement is to filter out any null or blank data values that could cause the script to error out on execute.
if($val["longdate"] != null || $val["longdate"] != ''){
$val["longdate"].getMonth()+1}
else{0}
Convert an entire column to just the month or number
($val["ColumnName"]).getMonth()+1;
With some additional modifications, we can even display the month name in place of number, as in the following example:
else if (m==2) {'February'}
else if (m==3) {'March'}
else if (m==4) {'April'}
else if (m==5) {'May'}
else if (m==6) {'June'}
else if (m==7) {'July'}
else if (m==8) {'August'}
else if (m==9) {'September'}
else if (m==10) {'October'}
else if (m==11) {'November'}
else {'December'}
else if (m=='February'||m=='Feb') {'2'}
else if (m=='March'||m=='Mar') {'3'}
else if (m=='April'||m=='Apr') {'4'}
else if (m=='May') {'5'}
else if (m=='June'||m=='Jun') {'6'}
else if (m=='July'||m=='Jul') {'7'}
else if (m=='August'||m=='Aug') {'8'}
else if (m=='September'||m=='Sept') {'9'}
else if (m=='October'||m=='Oct') {'10'}
else if (m=='November'||m=='Nov') {'11'}
else {'0'}
Automatic large number abbreviation
Formatted for iDashboards
Input example: Datatype: Number - 1,000,000
Output Example: Datatype: String - 1M
This expression was created for the Details Chart but can be used to abbreviate long numbers for view in legend. This expression will take a long number such as 1,000,000 and convert it to a string value of 1M. You can add this value to your legend as a clean looking rollover value.
//nbr = axis of #
//d = # of deci places
//s = curr symbol
//n = set 1 to return "0" for null value
var nbr = y1 < 0 ? y1*-1 : y1
d = 1,
s = y1 < 0 ? "-$" : "$"
n = 1;
//do not edit below this line
function abbrNum(r,a){a=Math.pow(10,a);for(var t=["K","M","B","T"],n=t.length-1;n>=0;n--){var e=Math.pow(10,3*(n+1));if(e<=r){1e3==(r=Math.round(r*a/e)/a)&&n<t.length-1&&(r=1,n++),r+=t[n];break}}return r}
var z=abbrNum(nbr, d);
if (z==null&&n==1){s+'0'}else if(z==null&&n==0){null}else{s+z}
Generating a Random Number (Partner Submitted)
// Returns a random number:
Math.random();
// Returns a random integer from 0 to 9:
Math.floor(Math.random() * 10);
Generating a Random Number Between -.5 and .5
(Math.random()*1)-.5
Generating a UUID when Structuring Data in Datahub (Partner Submitted)
//Creates a New Date Object with the Current Date and Time:
new Date()
//Get the time (in milliseconds since January 1, 1970)
getTime()
//Returns x rounded down to its nearest integer
Math.floor(x)
//Functions
function name(parameter1, parameter2, parameter3) {
// code to be executed
}
//Converts Numbers to Strings
toString()
Generating UUID
var dt = new Date().getTime();
var uuidx = 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
var r = (dt + Math.random()*16)%16 | 0;
dt = Math.floor(dt/16);
return (c=='x' ? r :(r&0x3|0x8)).toString(16);
});
uuidx
For More Information:
- iDashboards Builder Manual 14 Analytics
- iDashboards Data Hub Manual 8. Extract, Transform and Load
- OSKAR: Data Analytics
Disclaimer: iDashboards Technical Support Engineers are not Data Analysts who know in depth SQL Queries or JavaScript Expressions. We often learn these skills on the job and have limited knowledge. We do our best to help you with your software in determining if: you are not leveraging the software in the best way for your data or you found a bug in the software, because we want to assist you in your success. If you have issues with in depth queries or expressions please contact iDashboards Support and know that it will take a bit of time to figure out what is necessary for this issue, and if necessary we will refer you to your Client Success Manager to schedule time with a Principal Product Engineer.
If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.
Comments
0 comments
Please sign in to leave a comment.