Window Transformations

You can use windowing functions with the following transformation types:

ROLLINGAVERAGE (Sales, 0,1)

Sales

You may need to create windows of time within your data that are not cleanly segmented by basic units of time measurement. For example, you may need to create a custom time period, called a session, based on timestamps recorded in event-based data.

A session is usually defined as a group of events that occur within a given time frame. For example, you may need to perform calculations based on five-minute intervals within your logging data. If a user opens your shopping website, logs in, searches items, and then logs out within a five-minute interval, that can be grouped under a single session. However, if the user's interaction lasted six minutes, the logged events may span multiple windowed sessions in the data.

You can use the SESSION function to create time boxes based on a time period that you specify. When the function is applied to your column of timestamp values, the application assigns an ID to events that belong to the same session.

From the following example, you can create a Session ID. After you create the session ID, you can find the volume of data consumed by the individual user.

Source:

Transformation:

SESSION (TimeStamp, 5, minute)

User Name

TimeStamp

Since the new column is named window, you should rename it:

Manual rename

window

SESSIONID

With this session ID, you can calculate the maximum volume of data consumed by each session ID and by each user.

Multiple row formula

MAX(Volume (in Kb))

SessionID

User Name, SessionID

Volume_Consumed (in Kb)

Results:

The PREV and NEXT functions enable you to fetch data from a previous row or a subsequent row, which is helpful for identifying relative changes or trends in your data.

Source:

The following dataset contains orders for different product types over a given time period. You can apply the PREV and NEXT functions to calculate the previous orders and the next orders to analyze the trend of orders and derive the average of orders for a product group.

Transformation:

You can also calculate the percentage of change in orders over time. The following transformation calculates the change between the current order and the previous one and then divides that value over the previous value to calculate the percent change between the rows:

(Order - PREV(Order, 1)) / PREV(Order, 1) * 100

Product_Type

Order

After you rename the column to ChangeinOrder, you can apply the NUMFORMAT function to clean up and format the ChangeinOrder values. The following transformation reformats the ChangeinOrder column to display two decimal places.

ChangeinOrder

NUMFORMAT(ChangeinOrder, '##.##')

Similarly, you can apply the NEXT function and calculate the Change in orders for upcoming months.

Results:

You can use the FILL function to fill empty or null values in your data with the last non-empty value in the group.

Source:

For example, the following dataset contains the daily orders received. Note the missing values due to weekends. You can assume that the no orders were received for Saturday and Sunday ,

Transformation:

You have to clean up the data to fill the values for OrdersDay column. You can use the following function to fill the empty and null values. This function tests the the OrdersDay column to check if the column is empty or null. If so, the value '0' is written in the column, else the value of the column ($col) is written.

OrdersDay

IF(OrdersDay == '' || ISNULL(OrdersDay), '0', $col)

You can see the values of Friday is taken for Saturday and Sunday and filled it accordingly as per theFILLfunction.

OrdersTotal

IF (OrdersDay == '0', FILL (OrdersTotal, -1,0),$col)

Date

Results:

The RANK function enables you to create rankings in your data based on calculations by returning a ranking value for each row with the specified group of values. When used, some rows might receive the same value as other rows. For example, if there are three tie values in a group, the same rank is assigned to the rows and the next three ranks are skipped.

The DENSERANK function enables you to generate a ranked order of values within a group. If there are tie values in a group, it does not skip rank in case of tie values. For example, if two rows are listed as rank 2, then the fourth row receives rank 3.

Source:

The following dataset contains total Sales information by quarter. You can use the RANK and DENSERANK to identify the quarters with the highest sales.

Transformation:

RANK:

Multiple row formula

RANK()

Sales

SalesRank

DENSERANK:

Multiple row formula

DENSERANK()

Sales

SalesDenseRank

Results:

For the RANK function, when multiple rows share the same rank, the next rank is not consecutive, whereas for the DENSERANK function, the next rank is consecutive.

Rolling calculations enable you to compute a function over a changing set of rows. Rolling calculations are useful for computing the current state of a measure within your data.

For example, in the above sample data, you can find the rolling sum and rolling average of the sales for the year. You can use the above example data to find the rolling sum and rolling average.

Source:

From the following dataset, you can calculate the rolling calculations such as ROLLINGSUM, ROLLINGAVERAGE, ROLLINGMAX, and ROLLINGMIN.

Transformation:

ROLLINGSUM (Sales, 0,1)

ROLLINGAVERAGE (Sales, 0,1)

ROLLINGMAX (Sales, 0, 1)

ROLLINGMIN (Sales, 0,1)

Sales

You can rename the required columns accordingly.

Results:

ROLLINGSUM (Sales, 0,1)

ROLLINGMAXDATE (Order_date, 0,1)

ROLLINGMINDATE (Order_date, 0, 1)

ROLLINGMODEDATE (Order_date, 0,1)

Order_date