ArticlesBite Size Analytics: How to Build a Lens (Hands-On Exercise Guide)

A lens is now created and saved. Keep reading to learn how to add detail to the new lens.

Exercise 2: Group by Trial Balance 1 & 2 and General Ledger Name

1. Click + in the Group By section (the Data tab is at the far left with tooltip “Add column”)
2. Search and select the following dimensions (click “+” in the Group By section to add additional dimensions):
   • Trial Balance 1 (do not choose Local Trial Balance 1)
   • General Ledger Name
   • Trial Balance 2 (do not choose Local Trial Balance 2)
3. Drag the Trial Balance 2 field above the General Ledger Account Name in the Group By section to reorder the columns from left to right to be Trial Balance 1, the Trial Balance 2, then General Ledger Account Name
4. Click Save in the Object Header (Disk Icon) > Save

The groupings on the lens is now reorganized. Keep reading to learn how to add value columns to the report.

Exercise 3: Add Measure Columns to the Lens

Now add value columns to the reports. These are known as measures within Einstein Analytics. A lens can be thought of as an aggregation of all data within that dataset for those grouping criteria. If the user is a multi-company organization, the lens is for all companies. This can be changed later, but for now, the lens is an aggregate of all companies. Now follow the steps below to add dual currency values that make sense (for example, wanting to create a lens that adds USD to GBP):

1. Click Count of Rows under the Data tab (far left frame)
2. Click Sum
3. Locate and click Dual Value measure
4. Click + in the columns section
5. Click Sum
6. Locate and click Dual Value measure
7. Click the dropdown in the left frame on the first Sum of Dual Value (it has a blue handle)
8. Click Edit this Column
9. In the column Alias, type Period
10. In the column Name, type Period
11. Click Apply

The column heading now states Period for the first measures column in the table. To change the heading of the other column:

1. Click the dropdown
2. Click Edit this Column
3. In the column Alias enter Year_to_Date (either type the word together or use underscores when entering the alias)
4. In the column Name enter Year to Date
5. Click Apply

Click the Measures dropdown to see an option to clone a column. Rather than clicking the plus icon and adding the column twice, users can also change the first Dual Value column, edit its name, then click Clone. This generates a second measures column. To save time in the future, add the formula etc to a column to use the clone feature.

Now save this lens:

1. Click Save in Object Header (Disk Icon)
2. Click Save

Exercise 4: Filtering the Lens.

Now filter the lens to give it more context. First, to make this an Income Statement, only include data that relates to the Income Statement rather than Income Statement, Balance Sheet, etc. There are many ways to do this, but the easiest is to use the General Ledger Account Type and filter ALL of the lens by this type. When filtering all of the lens, it is called a Global filter:

1. Click the Filters tab
2. Click +
3. Click General Ledger Account Type
4. Select Profit and Loss
5. Click Add

Repeat the process above to add more filter criteria (for example Company) to reduce the data displayed in the table further. When a filter is added, the circle with the number of filters applied is shown next to the filter tab. Note all dropdowns presented are multi-select; so it is possible to select Profit and Loss as well as Retained Earnings.

Click the Data tab to review the data, then filter the data shown in each of the Measures columns. Currently the data is the same in both columns:

1. Click the Period Measure dropdown 
2. Click Add filter
3. Select Financial Year and Period(s) in the measure section
4. Click Equals in the operator dropdown
5. Enter 2019012 in the values box
6. Click Add

The financial year and period looks odd formatted as a number rather than as a date. However, this is incredibly useful as it enables users to perform simple math such as "greater than", "equals to" , "less than", and so on. These operations would not be possible as a date format. 

Now filter the Year to Date column:

1. Click the Year to Date Measure dropdown
2. Click Add filter
3. Select Financial Year and Period(s) in the measure section
4. Click Less than or equal to in the operator dropdown
5. Enter 2019012 in the values box
6. Click Add

This now filters the year to date column to show all data up to and including period 12 2019. Save the lens:

1. Click Save in Object Header (Disk Icon)
2. Click Save

Exercise 5: Sorting and Formatting the Lens

Within the Measures column, each number is displayed with decimal precision. This makes it difficult to read. To format the number so a decimal precision is not shown:

1. Click the dropdown on the Period Measure
2. Click Format Numbers
3. Click Custom
4. Select the second preset 1,235
5. Click Done

The Period column is now formatted in a sensible way. Now do the same for Year to Date:

1. Click the dropdown on the Year to Date Measure
2. Click Format Numbers
3. Click Custom’
4. Select the second preset 1,235
5. Click Done

Now order the grouping columns. Ideally these are based on the picklist order for the Trial Balance levels that were set in Salesforce. For an Income Statement use the following order:

1. Sales
2. Cost of Sales
3. Gross Profit
4. Expenses

However, the above order is not alphanumeric ascending or descending. To fix this within our datasets for Financial balances and transactions, the measure columns TB1 Order -TB 4 Order is introduced. In principle, each picklist value within a trial balance level is given a number dependent on its order in the picklist. This means users can sort these columns and get their order. Lens contain Trial Balance levels 1 and 2. Certinia recommends using TB levels as these provide a financial reporting structure that minimizes excessive maintenance:

1. Click + in the Columns section
2. Click Maximum
3. Select TB 1 Order measure
4. Select TB 2 Order measure
5. Click the dropdown on the TB1 Order measure
6. Click Sort ascending
7. Click the dropdown on the TB2 Order measure
8. Click Sort ascending

Two new columns have been added and the order is now complete. Now hide these columns on the lens:

1. Click the dropdown on the TB1 Order measure
2. Click Hide
3. Click the dropdown on the TB2 Order measure
4. Click Hide

The columns are now hidden. Save the lens:

1. Click Save in Object Header (Disk Icon)
2. Click Save

Exercise 6: Exporting The Lens

Now share the lens so it can be included in the board pack or format the data in a spreadsheet tool, etc: 

1. Click the More icon in Object Header (three dots Icon)
2. Click Share
3. Click the Download tab
4. Click the appropriate option (CSV, Excel etc)

Create a Rolling Trial Balance

1. Navigate to the App Launcher
2. Click Analytics Studio
3. Click All Items under Browse header
4. Open the Analytics app created during implementation
5. Click Datasets > Financial Transactions to open

Create a new lens that:

• Groups by General Ledger Name and Transaction Date
• Has three measures columns: Opening, Net, and Closing
• Contains measures filters as shown in the image. Remember to use Financial years and period(s) measure to perform less than type operations
• Save the lens and name it "Rolling TB"

To clean-up the look of the lens, click on the roller icon on the far right and select format tables.

To add the rolling column:

1. Click the dropdown on the Net measure
2. Click Clone
3. Click Edit

Change the name and Alias to "Rolling_Trial_Balance". Remember to add underscores for the Alias:

1. Click Function Icon (mathematical symbol)
2. Click Running total
3. Click on the Column dropdown > select Net
4. Click on the Reset group dropdown > select General Ledger Name
5. Click on the Format dropdown > select Custom
6. Click 1235
7. Click Done
8. Click Apply
9. Click Close

Now there is a running total on the lens. Remember to save it. Click Total on the top right of the table. This allows users to toggle on and off a grand total. Unfortunately, the grand total is displayed at the top rather than the bottom of the table. Be aware that the rolling total grand total is meaningless.