RANK AND DENSE_RANK – SuiteID: 31414
• Rank – Computes the rank of a value in a group of values. This can result in non-consecutive rankings if values are the same.
• Dense_Rank – Computes the rank of a value with respect to other values and returns the rank as a NUMBER. It always results in consecutive rankings.
> Navigate to Reports → Saved Search → All Saved Searches → New
> Select saved search type ‘Transaction’
> Although DENSE_RANK and RANK are logical SQL expressions, they cannot be used in Saved Searches either as Criteria or in the Results tab when combined with a Summary Type.
Enter Title and ID for the Search. Under the Criteria tab, select Type Bill and Main Line as True.
In the Sorting tab, select Date and check descending box. Under the Result tab, add the following:
- Formula (Date) = CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {trandate} DESC))<4 THEN {trandate} END
- Formula (Text) = CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {trandate} DESC))<4 THEN {mainname} END
- Formula (Currency) = CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {trandate} DESC))<4 THEN {amount} END
This means that the Bills are sorted by Transaction Date in descending order per Vendor and the Transaction date, Name of the Vendor and Bill amount will be displayed only for the latest 4 bills per vendor. That is, although all bills would be displayed in the search, only the details of the latest 4 bills would be visible.
The following Saved Search result was obtained. If the list is to be sorted by Vendor, then it can be added to the sorting criteria.
- LAST_DAY – returns the date of the last day of the month that contains the date
In the previous SQL formula in Bill search, we will add an additional column for the last date of the month in which the transaction date is. In the result tab, add Formula(date) as LAST_DAY({trandate}) and click Save.
- NEXT_DAY – returns the date of the first weekday named by char that is later than the date In the SQL formula in Bill search, we will add another column showing the date of Friday after the transaction date. In the result tab, add Formula (date) field and enter formula NEXT_DAY({trandate}, ‘FRIDAY’), and click Save.
- CASE WHEN TO_CHAR -returns a value based on different conditions
> Create a new search with type transaction.
> Select Formula [numeric] as a field under the Result tab. Enter formula CASE WHEN TO_CHAR({datecreated},’D’)=2 THEN {internalid} END for Monday, CASE WHEN TO_CHAR({datecreated}, ‘D’)=3 THEN {internalid} END for Tuesday and so on. Select Summary type as Count.
- > Enter Memorized – False criteria and Mainline-True criteria and click Save. This avoids the deleted transactions from being included in the count of per-day transactions and gives the accurate existing transaction count. Thus, the search result shows the precise number of transactions for each working day.
- EXTRACT – extracts and returns the value of a specified DateTime value from a DateTime or interval value expression. This function can be very useful for manipulating date-time values in very large variables.
> To study the extract function, an existing Dataset named “Bills of 2022′ is taken as the Extract function is not available in Saved Search in the test account.
Add Formula EXTRACT(Month FROM {trandate}) with Output type as Integer and insert the formula to the dataset.
> Click Save
- TRUNC_DATE – truncate the date in the format defined and provide the result with the beginning of the time corresponding to that date.
> In the ‘BILLS OF 2022’ Dataset, add new formula field with the formula TRUNC({trandate}, ‘MM’) and output type DATE. Insert this formula into the dataset to obtain the beginning of the month corresponding to each Bill date.
> The function has also been tested in Saved Search using the formula: TRUNC({trandate},’MM’)
- ADD_MONTHS – returns the date plus or minus integer months
> In the ‘BILLS OF 2022’ dataset, add formula ADD_MONTHS({trandate},1) with output type DATETIME and insert it in Workbook.
The same has been tested in Saved Search using Formula (date/time) – ADD_MONTHS({trandate},1)
- TO_CHAR -returns the value in character format
In the Bills of 2022 Dataset, add formula TO_CHAR({trandate}, ‘Month DD, YYYY’) with output type STRING.
The same function has been tested in Search using formula (text) TO_CHAR({trandate},’YYYY’).
- Weekend Days
In the Saved Search, using the Formula (Numeric) (((NEXT_DAY({today}, ‘Monday’))-(NEXT_DAY({today}, ‘Saturday’)))) the number of days in the weekend has been obtained.
- Weekdays
In the Saved Search, using the Formula (numeric) (7-(((NEXT_DAY({today}, ‘Monday’))-(NEXT_DAY({today}, ‘Saturday’))))) the usual number of working days in a week is obtained.
- FORMULA USING CASE WHEN, TO_CHAR & TRUNC
> In the Saved Search, add formula (text) as CASE WHEN (TO_CHAR({trandate}, ‘DD’)) = (TO_CHAR(TRUNC({trandate}, ‘MM’), ‘DD’)) THEN ‘YES’ ELSE ‘NO’ END to obtain the result whether the transaction is on 1st of the month.
> In order to highlight the transactions whose date is on the first day of the month, under the Highlighting tab enter the formula [text] CASE WHEN (TO_CHAR({trandate}, ‘DD’)) = (TO_CHAR (TRUNC({trandate}, ‘MM’),
‘DD’)) THEN ‘YES’ ELSE ‘NO’ END and select IS YES as the formula text criteria. Click Set.
Then click SET again. Select the Bold check box and click Save.
Thus, the results with Yes as an answer for the column will be highlighted as Bold.
- COALESCE – returns the first non-null expr in the expression list
For the Coalesce function, create a search and enter COALESCE(NULL,{totalamount}) as Formula (text). Note that NULL does not mean zero value. NULL is not a value but a state. So whatever value comes after null will be displayed as result. Here the amount value will be displayed in the result column as it comes after Null.
Suppose the formula was COALESCE(NULL, 1, {totalamount}) then the result would have displayed 1 in the result column.
- GREATEST – returns the largest expression in a list of expressions
- LEAST – returns the smallest expression in a list of expressions
For the purpose of illustrating of GREATEST and LEAST functions in the dataset, an Item type dataset has been created. The columns Item name, location, and quantity on hand have been added. The Item Headset has been filtered using the filter region.
GREATEST – GREATEST(105,20,50) with output type FLOAT.
LEAST – LEAST(105,20,50) with output type FLOAT.
We shall now apply the Greatest and Least formulas to this dataset.
