In this part of the tutorial we will extend the 'Lemonade store' from we have created in the first part. We will add another hypersheet to store price information for our products and will compute the revenue based on the 'items sold' information that we already have.

Open the document

We will start from the document we have created in the Part 1 of the tutorial.

  1. You can download a backup of the document we have created in the last part of the tutorial here. Use the 'Upload a document' button on the landing page to create a new document based on the file.

Create a 'Unit price' hypersheet

Let's create a second hypersheet to store unit price for each product. We will assume that our prices are fixed over the year and only depend on the product and the stand where the item was sold (so 1st Street and 2nd Street can have different prices for the same product).

  1. We will give a better name to the existing hypersheet first. Click the Hypersheet->Rename menu item and rename the 'Hypersheet 1' to 'Sales'. Leave the short name as is (H1).

  2. Now open the current hypersheet dropdown at the top of the screen and select 'New...'.

  3. Select only 'Product' and 'Salesperson' and click 'Ok'.

  4. A new hypersheet has been created. Rename it to 'Unit price' using the Hypersheet->Rename menu item.

  5. As you see we have all 4 salespeople and the 'Total' element in our Salesperson axis. As we said we only want to store unit prices on the street level. We also don't want to aggregate prices. Let's hide cells which do not make sence for this hypersheet. Select the 'Total' row, open the context menu and click 'Hide cells...' menu item

  6. The area of effect panel will pop up. Make sure the 'Total' element is selected for the Salesperson axis and click 'Save'.

  7. We have just created a first hidden cell layer, which removed the 'Total' element from this sheet. To remove the salespeople select any of the remaining cells, open the context menu and select 'Hide cells...' again.

  8. Select 'all items' along the product axis and 'all elements not having child elements' along the salesperson axis. After you click 'Save' all the leaf items (items without child items) will be removed.

  9. Now we only have 2 streets along the salesperson axis. Fill in some values like on the screenshot

Add an 'Account' axis to the Sales hypersheet

We need to make cells for the computed revenue values now. We will store them alongside the 'Items sold' values we already have in the 'Sales' hypersheet. We will create a new axis to distinguish between 'Items sold' and 'Revenue' values.

  1. Use the hypersheet dropdown at the top of the screen to return to the 'Sales' hypersheet.

  2. Make sure that hypersheet 'Sales (H1)' is selected and click the 'Hypersheet->Add axis' menu item to add an axis. Confirm with Ok.

  3. A new axis has been added to the hypersheet. All the existing values are assigned to the first element of the new axis. Double click on a newly created axis to open the axis screen

  4. Rename the 'Axis A' to 'Account'. The shortcut 'A' suits us fine. Give the first 2 axis elements the names 'Items sold' and 'Revenue'.

  5. Delete the last element as we don't need it. Right click on a cell from the last row and choose the 'Delete row...' from the context menu. Click 'Yes' to confirm.

  6. Click 'Back' button at the top left corner to return to the worksheet view.

Create a formula for the 'Revenue' element

We are ready to compute the revenue now. Let's add our first formula.

  1. To get a better overview of our worksheet align the axis as shown on the screenshot. You can drag and drop axis labels between rows, columns and filter.

  2. Now select the Jonny's revenue cell and choose 'New formula..' from the context menu.

  3. You see an empty formula panel at the bottom of the screen. We want to multiply the number of items solds by the price of the product to get the revenue. Click on the Jonny's 'Items sold' cell (marked with an arrow on the screenshot) to add a cell reference to the formula.

  4. [A:1] has been added to the formula. 'A' is the shortcut of the 'Account' axis, '1' is the identifier of the 'Items sold' axis element. Now type '*' for the multiplication.

  5. Lets add the price from the 'Unit price' hypersheet as the second factor. Use the hypersheet dropdown at the top of the screen to select the 'Unit price' hypersheet. Now click on the Lemonade price on the 1st Street.

  6. H2.[S:parent()] has been added to the formula. 'H2' is the shortcut of our 'Unit price' hypersheet. 'S' is the shortcut of the 'Salesperson' axis. 'parent()' axis function returns the parent element of the current element. You could also have typed the formula as text.

  7. Use the hypersheet dropdown to select the 'Sales' hypersheet again. As you see the correct revenue of 5,98 has been computed. Now click the 'Area of effect' button to define the area of effect for the formula.

  8. We want the formula to be applied to all products and all months, but only to 'all elements not having child elements' (these are the names of the salespeople) on the salesperson axis and only to 'Revenue' account. Choose the axis filters as shown on the screenshot and click 'Save'.

  9. As you see the revenue columns are grayed out now to indicate that the values are computed.

  10. Congratulations! You have finished the second part of the tutorial. You can download a backup of the part 2 document here.