You can use familiar SAQL syntax to create your own formula for a column of multiple columns in a table or visualize it to a chart.
On top of manually building custom formula with SAQL, Einstein Analytics also provide defined windowing functions to analyze data across rows.
Sliding Window
Applies an aggregate function to the current row with respect to a configurable range of rows.
- Column: source data (a column)
- Function: Average, Sum, Min, Max
- Start: start row to analyze, -1 mean 1 previous row from the current row
- End: end row to analyze, 0 mean current rows, 1 mean 1 row after current row
- Reset Group: if only you have more than 1 grouping, you can reset by
Let us see a sample:
Once saved, we will see the formula
max(A) over ([-1..0] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day'))
Now, let us add Account Name as 2nd level grouping without adding Reset Group
Because there are 3 accounts on 13-May-2017, the row explodes to 3 lines, but the logic still the same, which is comparing max value between the current and previous row. In this change, the formula will not change.
Now, let us add Reset Group = Opty.CloseDate
The formula will be changed by adding Account Name (in this sample the API name is "Name")
max(A) over ([-1..0] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day','Name'))
Because Close Date added as Reset Group, windowing functions only compare within the same date.
Percentage of Group
Calculates the percentage each row is of its group total, or of the grand total. The percentage only applicable for data shown in the table, NOT for the whole data in dataset (if you apply filter).
The formula: A/sum(A) over ([..] partition by all)
The function can be reset on a grouping defined in the table, this is the same with Sliding Window, you need to have minimum 2 grouping to apply Reset Group.
The formula: A/sum(A) over ([..] partition by 'Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day')
Rank Within Group
There are 4 functions offered by Rank Within Group:
- Rank: assigns rank based on order. Repeats rank when the value is the same, and skips as many on the next non-match
- Dense Rank: same as rank() but doesn’t skip values on previous repetitions.
- Cumulative Distribution: calculates the cumulative distribution (relative position) of the data in the reset group
- Row-number: assigns a number incremented by 1 for every row in the reset group.
Formulas for each function:
- Rank: rank() over([..] partition by all order by A desc)
- Dense Rank: dense_rank() over([..] partition by all order by A desc)
- Cumulative Distribution: cume_dist() over([..] partition by all order by A desc)
- Row Number: row_number() over([..] partition by all order by A desc)
The Order menu determines the direction of ranking based on the values being ranked. Ascending ranks the lowest value as number 1, while descending ranks the highest value as number 1. Same as Sliding Window and Percentage of Group function, we can add Reset Group in Rank Within Group.
Period Over Period
To use Period Over Period, the table must be group by Date field. Period Over Period function compare periods of time to calculate changes in values, for example: year-over-year, quarter-over-quarter, month-over-month, week-over-week, or day-over-day.
Then, we have option to show the result as: % Change or Unit Change.
Formula for % Change
(A - sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day')))/(sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day')))
Formula for Unit Change
A - sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day'))
Period Over Period do not offer Reset Group.
Change from Previous
Compares the value of the current row with that of the previous row and calculates the difference. This function similar with Period Over Period, but you do not need to group by Date.
Change from Previous offer Reset Group function and similar with Period Over Period, we have option to show the result as: % Change or Unit Change.
Formula for % Change
(A - sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day')))/(sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day')))
Formula for Unit Change
A - sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day'))
Above table looks very similar with Period Over Period, but Change from Previous can be implemented to any grouping, not just Date over a period.
Running Total
Calculates the total value of the current row summed with all previous rows.
Running Total formula without Reset Group: sum(A) over ([..0] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day'))
Running Total also offer Reset Group if you have more than 1 grouping in table. Let us see in sample with and without reset Group:
Reference:
No comments:
Post a Comment