Page History
...
| Function | Usage | Syntax | Example | Example Result |
|---|---|---|---|---|
| Day Difference | Returns the difference between <End Time> and <Start Time> in days | DDIFF(<End Time>, <Start Time>) | DDIFF(Discharge Time, Admission Time) | Returns the difference in days from the time of admission to hospital to the time of patient discharge. |
| Month Difference | Returns the difference between <End Time> and <Start Time> in months. Returns whole numbers. | MDIFF(<End Time>, <Start Time>) | MDIFF(Arrival Time, Departure Time) | Returns the difference in months from the time a ship departures from its departure port to the time of arrival in its destination port. |
| Quarter Difference | Returns the difference between <End Time> and <Start Time> in quarters. Returns whole numbers. | QDIFF(<End Time>, <Start Time>) | QDIFF(End Semester, Start Semester) | Returns the difference in quarters from the first academic semester to the graduation semester. |
| Year Difference | Returns the difference between <End Time> and <Start Time> in years. Returns whole numbers. | YDIFF(<End Time>, <Start Time>) | YDIFF(Sentence End, Sentence Start) | Returns the difference in years from sentence start to sentence end. |
| Second Difference | Returns the difference between <End Time> and <Start Time> in seconds. | SDIFF(<End Time>, <Start Time>) | SDIFF(Leaving Time, Landing Time) | Returns the difference in seconds from the time of landing on the page to the time of leaving the page. |
| Minute Difference | Returns the difference between <End Time> and <Start Time> in minutes. | MNDIFF(<End Time>, <Start Time>) | MNDIFF(Payment Completed Time, Landing Time) | Returns the difference in minutes from the time of landing on the page to the time of completing payment. |
| Hour Difference | Returns the difference between <End Time> and <Start Time> in hours. Returns whole numbers. | HDIFF(<End Time>, <Start Time>) | HDIFF(Attendance Time, Check-in Time) | Returns the difference in hours between the check-in time to the Emergency Room and time of attendance by the doctor. |
| Past Week Difference | Returns the difference between this week's data and the data from the previous week. Use this function when the time resolution used in your widget is day or week. | DIFFPASTWEEK(<numeric field>) | DIFFPASTWEEK(Total Sales) | Returns the difference between this week's sales and previous week's sales, for the displayed time resolution. For example, for day resolution: (sales in current day - sales in same day one week back). For week resolution: (sales in current week - sales in previous week) |
| Past Month Difference | Returns the difference between this month's data and the data from the previous month. | DIFFPASTMONTH(<numeric field>) | DIFFPASTMONTH(Total Sales) | Returns the difference between this month's sales and previous month's sales, for the displayed time resolution. |
| Past Quarter Difference | Returns the difference between this quarter's data and the data from the previous quarter. | DIFFPASTQUARTER(<numeric field>) | DIFFPASTQUARTER(Total Sales) | Returns the difference between this quarter's sales and previous quarter's sales, for the displayed time resolution. |
| Past Year Difference | Returns the difference between this year's data and the data from the previous year. | DIFFPASTYEAR(<numeric field>) | DIFFPASTYEAR(Total Sales) | Returns the difference between this year's sales and previous year's sales, for the displayed time resolution. |
| Past Period Difference | Returns the difference between this period's data and the data from the previous period, using the current time resolution. | DIFFPASTPERIOD(<numeric field>) | DIFFPASTPERIOD(Total Sales) | Returns the difference between this period's sales and previous period's sales. |
| Growth | Calculates growth over time. (0% baseline) Accepts any time resolution (day, week, etc.) in the widget. | GROWTH(<numeric field>) | GROWTH(Total Quantity) | If this month your Total Quantity is 12, and last month it was 10, your Growth for this month is 20% (0.2). Calculation: (12 – 10) / 10 = 0.2 If this year your Total Quantity is 80, and last year it was 100, your Growth for this year is -20% ( -0.2). Calculation: (80 – 100) / 100 = -0.2 |
| Growth Rate | Calculates growth over time. (100% baseline) Accepts any time resolution (day, week, etc.) in the widget. | GROWTHRATE(<numeric field>) | GROWTHRATE(Total Quantity) | If this month your Total Quantity is 12, and last month it was 10, your Growth for this month is 120% (1.2). Calculation: 12/ 10 = 1.2 If this year your Total Quantity is 80, and last year it was 100, your Growth for this year is 80% (0.8). Calculation: 80 / 100 = 0.8 |
| Growth Past Week | Calculates the growth from the past week to the current week. Use this function when the time resolution in your widget is weeks or days. | GROWTHPASTWEEK(<numeric field>) | GROWTHPASTWEEK(Total Sales) | Calculates the difference between this week's sales and previous week's sales, for the displayed time resolution. |
| Growth Past Month | Calculates the growth from the past month to the current month. Use this function when the time resolution in your widget is month or day. | GROWTHPASTMONTH(<numeric field>) | GROWTHPASTMONTH(Total Sales) | Calculates the difference between this month's sales and previous month's sales, for the displayed time resolution. |
| Growth Past Quarter | Calculates the growth from the past quarter to the current quarter. Use this function when the time resolution in your widget is month or quarter. | GROWTHPASTQUARTER(<numeric field>) | GROWTHPASTQUARTER(Total Sales) | Calculates the difference between this quarter's sales and previous quarter's sales, for the displayed time resolution. |
| Growth Past Year | Calculates the growth from the past year to the current year. | GROWTHPASTYEAR(<numeric field>) | GROWTHPASTYEAR(Total Sales) | Calculates the difference between this year's sales and previous year's sales, for the displayed time resolution. |
| Prev | Returns the Time period Member in <Time Field> which is N periods back from the current member. | ((<numeric field>), PREV(<Time Field>, [<N>])) | ([Total Quantity], PREV([Months in Date], 2)) | Returns the Total Quantity value for the month that occurred two months ago. |
| Next | Returns the value for the time-period member in <Time Field> which is N periods after the current member. | ((<numeric field>), NEXT(<Time Field>, [<N>])) | ([Total Quantity],NEXT([Months in Date], 2)) | Returns the Total Quantity value for the month occurring two months ahead. |
| Now | Returns the value for the current time period. | ((<numeric field>), NOW(<Time Field>)) | ([Total Quantity],NOW([Months in Date])) | This formula returns the Total Quantity value for the current month. |
| Past Day | Returns the value for the previous day. | PASTDAY(<numeric field>) | PASTDAY(Total Sales) | Returns the Total Sales value one day back. |
| Past Week | Returns the value for the same period in the previous week. | PASTWEEK(<numeric field>) | PASTWEEK(Total Sales) | Returns the Total Sales value one week back for the displayed time resolution. |
| Past Month | Returns the value for the same period in the previous month. | PASTMONTH(<numeric field>) | PASTMONTH(Total Sales) | Returns the Total Sales value one month back for the displayed time resolution. |
| Past Quarter | Returns the value for the same period in the previous quarter. | PASTQUARTER(<numeric field>) | PASTQUARTER(Total Sales) | Returns the Total Sales value one quarter back for the displayed time resolution. |
| Past Year | Returns the value for the same period in the previous year. | PASTYEAR(<numeric field>) | PASTYEAR(Total Sales) | Returns the Total Sales value one year back for the displayed time resolution. |
| Range | Returns an accumulated value for a data set where two members of the same dimension and level define the minimum and maximum values of the range. | RANGE(<Start Date>, <End Date>) | ( [Total Quantity], RANGE ( PREV ( [Days in Date],2), current( [Days in Date] ))) | Returns the Total Quantity value in the range between two days ago and today. |
| Week to Date Average | Returns the running average starting from the beginning of the week up to the current day. | WTDAVG(<numeric field>) | WTDAVG(Total Sales) | Returns the running average of Total Sales starting from the beginning of the week up to the current day. |
| Week to Date Sum | Returns the running total starting from the beginning of the week up to the current day or week. | WTDSUM(<numeric field>) | WTDSUM(Total Sales) | Returns the running total of Total Sales starting from the beginning of the week up to the current day. |
| Month to Date Average | Returns the running average starting from the beginning of the month up to the current day. | MTDAVG(<numeric field>) | MTDAVG(Total Sales) | Returns the running average of Total Sales starting from the beginning of the month up to the current day. |
| Month to Date Sum | Returns the running total starting from the beginning of the month up to the current day or week. | MTDSUM(<numeric field>) | MTDSUM(Total Sales) | Returns the running total of Total Sales starting from the beginning of the month up to the current day. |
| Quarter to Date Average | Returns the running average starting from the beginning of the quarter up to the current day. | QTDAVG(<numeric field>) | QTDAVG(Total Sales) | Returns the running average of Total Sales starting from the beginning of the quarter up to the current day. |
| Quarter to Date Sum | Returns the running total starting from the beginning of the quarter up to the current day or week. | QTDSUM(<numeric field>) | QTDSUM(Total Sales) | Returns the running total of Total Sales starting from the beginning of the quarter up to the current day. |
| Year to Date Average | Returns the running average starting from the beginning of the year up to the current day. | YTDAVG(<numeric field>) | YTDAVG(Total Sales) | Returns the running average of Total Sales starting from the beginning of the year up to the current day. |
| Year to Date Sum | Returns the running total starting from the beginning of the year up to the current day or week. | YTDSUM(<numeric field>) | YTDSUM(Total Sales) | Returns the running total of Total Sales starting from the beginning of the year up to the current day. |
...
| Function | Usage | Syntax | Example | Example Result |
|---|---|---|---|---|
| All | Ignores the scope set on the dimension. This function can only work as a parameter inside another formula, and not by itself. | ALL(<numeric field>) | SUM(ALL(Items)) | Returns the sum of all items, ignoring filters. |
| Case | Returns the result_expression of the first condition evaluated as true. When no condition is true, else_expression is returned, if one is defined. | (WHEN <condition> THEN <result_expression> [ELSE <result_expression>] END) | CASE WHEN SUM(Sales) < 100 THEN 1 WHEN SUM(Sales) < 1000 THEN 2 ELSE 3 END | Returns '1' when Total Sales is less than 100, Returns '2' when Total Sales is less than 1000, Returns '3' in any other case |
| If | Returns numeric expression '1' when the condition is true, and expression '2' when the condition is false. | IF (<condition>, <numeric expression 1>, <numeric expression 2>) | IF(Count(Sales)>100, Sum(Sales)*1.1, sum(Sales)) | If the number of unique values within the Sales values is larger than 100, the function will return Total Sales x 1.1. Otherwise, returns only the Total Sales |
| IsNull | Returns true if the expression doesn't contain data (Null). | ISNULL(<numeric field>) | IF(ISNULL(Cost), 0, Cost) | If the Cost is null, returns 0. Otherwise, returns the cost. |
| Ordering | Returns the numeric order position of rows sorted into ascending or descending order, breaking ties with further arguments. The expressions must be aggregated by applying the MIN/MAX functions. | ORDERING(<expression1>,<expression2>) | ORDERING(MIN([Sales Person Name]), MIN([Days in Transaction_Date]), Sum([Sales])) | Returns the sum of sales, ordered by sales person descending, breaking ties with days in transaction date. |
...
Overview
Content Tools