Excel Sales Dashboard: behind the scenes

Download the Excel_Dashboard_Vizubi.zip from: http://www.vizubi.com/dwnld/Excel_Dashboard_Vizubi.zip
Extract the .xlsx and the .ndb files into the same directory.
Open the Excel_Dashboard_Vizubi.xlsx
You should see the dashboard in the image above.

The two pivot charts in the dashboard are built using the group Order Date YQMW field in the Row Labels pane and the SalesAmount and SalesOrderNumber fields (respectively for the left and the right chart) in the Values pane

For more details about groups view the How to use groups with your reports tutorial.

To build the Order Date YQMW group follow the steps in the How to create a Slicer tutorial and put the following fields in the slicer: Year, Year-Quarter, Year-Month, Year-Week.

The Top Ten tables are built with the Indirect and Concatenate Excel formulas; the data comes from other worksheets in the workbook.
In this example we will learn how The Top Ten Products by Revenue table was created.
The INDIRECT formula gets data from the Products Last 12 months worksheet and the References worksheet. The References worksheet contains values from 2 to 11. The Products Last 12 months worksheet contains sales for the top ten products by month for a year. By using the Indirect function, we tell Excel to get the value from a specific row (one of the values in the References worksheet which go from 2 to 11 because the first row contains a header), select the value in that row in the Products Last 12 months worksheet and put that value into our cell in the dashboard.

Click on the Products Last 12 months worksheet.

The table in the worksheet includes data calculated using two Vizubi Formulas:
(1) GetVizubiDataRow(Data_Field;Report;Data_Row)
(2) GetVizubiData(Data_Field;Report [;Field1;Item1 [;Field2;Item2 [;Field3;...]]])

The first formula gets the data from a specific row of a specific column in a Vizubi report which you have already created. The report does not have to be in your workbook; you can create it directly in the Vizubi database; this allows you to get data from reports with potentially millions of rows of results which would be impossible if the report were in Excel.

The second formula gets the value of a specific field in a report which is on the same row as a specific set of field-value pairs which are in the same report (e.g.; it can return the value for the Sales” field for the Country=USA; State=Illinois; City=Chicago field=value pairs).

The GetVizubiDataRow(Data_Field;Report;Data_Row) function returns the value of a field from the row specified. Combined with a report which is ordered by the target field, the formula allows you to display the first N values of any column in the report.
Data_Field is the field that contains the measure to return.
Report is the name of the report to use.
Data_Row: is the number of the which contains the value to return.

(1) Click on the cell A2: the syntax of the Vizubi formula is as follows: =GetVizubiDataRow("Product Name","Top n Products",References!$C2) where:
Data_Field is "Product Name".
Report is "Top n Products": it is a report that you can find available inside the ndb file that includes the "Product Name" field.
Data_Row is "References!$C2": the value is in the cell D2 of the References worksheet. The table in this worksheet is used to create the correct sorting for the Top ten tables.

(2) In the same way we have built the formulas in the cells of the first row (B1, C1, etc…) completing the formula as follows: = GetVizubiDataRow("Year-Month","Year-Month List",References!$D2).

The GetVizubiData(Data_Field;Report [;Field1;Item1 [;Field2;Item2 [;Field3;...]]]) function returns the value of a measure from a report using a combination of one or more dimensions (maximum 5). For example if you want to put the value of sales of a specific product in a cell in your report you can use this function.

Data_Field is the field that contains the measure to return.
Report is the name of the report to use.
Field1-Item1, etc…: the field name and value pair to use in the search. You must include a field-item pair for each dimension in the report.

(1) Click on the cell C4: the syntax of the Vizubi formula is as follows: = GetVizubiData("Sales","Sales by Product YM","Product Name",$A4,"Year-Month",C$1) where:
Data_Field is "Sales"
Report is "Sales by Product YM": this is a report that is in the ndb file.
Field1,Item1: are respectively Product Name and $A4.
Field2,Item2: are respectively Year-Month and C$1.

The data in the References worksheet are values used to define the row number in the Vizubi Functions. These are useful when you want to be able to copy the Vizubi functions in Excel to create top/bottom ten lists.

The twenty sparklines on the left of the dashboard are Excel graphs built using the series formula in Excel. The formula includes data from the Products Last 12 Months worksheet for the first 10 sparklines and the data from the Customers Last 12 Months worksheet.

If you need support or suggestion to personalize your dashboard, contact us at: support@vizubi.com

Download the Excel_Dashboard_Vizubi.zip from: http://www.vizubi.com/dwnld/Excel_Dashboard_Vizubi.zip
Extract the .xlsx and the .ndb files into the same directory.
Open the Excel_Dashboard_Vizubi.xlsx
You should see the dashboard in the image above.

The two pivot charts in the dashboard are built using the group Order Date YQMW field in the Row Labels pane and the SalesAmount and SalesOrderNumber fields (respectively for the left and the right chart) in the Values pane

For more details about groups view the How to use groups with your reports tutorial.

To build the Order Date YQMW group follow the steps in the How to create a Slicer tutorial and put the following fields in the slicer: Year, Year-Quarter, Year-Month, Year-Week.

The Top Ten tables are built with the Indirect and Concatenate Excel formulas; the data comes from other worksheets in the workbook.
In this example we will learn how The Top Ten Products by Revenue table was created.
The INDIRECT formula gets data from the Products Last 12 months worksheet and the References worksheet. The References worksheet contains values from 2 to 11. The Products Last 12 months worksheet contains sales for the top ten products by month for a year. By using the Indirect function, we tell Excel to get the value from a specific row (one of the values in the References worksheet which go from 2 to 11 because the first row contains a header), select the value in that row in the Products Last 12 months worksheet and put that value into our cell in the dashboard.

Click on the Products Last 12 months worksheet.

The table in the worksheet includes data calculated using two Vizubi Formulas:
(1) GetVizubiDataRow(Data_Field;Report;Data_Row)
(2) GetVizubiData(Data_Field;Report [;Field1;Item1 [;Field2;Item2 [;Field3;...]]])

The first formula gets the data from a specific row of a specific column in a Vizubi report which you have already created. The report does not have to be in your workbook; you can create it directly in the Vizubi database; this allows you to get data from reports with potentially millions of rows of results which would be impossible if the report were in Excel.

The second formula gets the value of a specific field in a report which is on the same row as a specific set of field-value pairs which are in the same report (e.g.; it can return the value for the Sales” field for the Country=USA; State=Illinois; City=Chicago field=value pairs).

The GetVizubiDataRow(Data_Field;Report;Data_Row) function returns the value of a field from the row specified. Combined with a report which is ordered by the target field, the formula allows you to display the first N values of any column in the report.
Data_Field is the field that contains the measure to return.
Report is the name of the report to use.
Data_Row: is the number of the which contains the value to return.

(1) Click on the cell A2: the syntax of the Vizubi formula is as follows: =GetVizubiDataRow("Product Name","Top n Products",References!$C2) where:
Data_Field is "Product Name".
Report is "Top n Products": it is a report that you can find available inside the ndb file that includes the "Product Name" field.
Data_Row is "References!$C2": the value is in the cell D2 of the References worksheet. The table in this worksheet is used to create the correct sorting for the Top ten tables.

(2) In the same way we have built the formulas in the cells of the first row (B1, C1, etc…) completing the formula as follows: = GetVizubiDataRow("Year-Month","Year-Month List",References!$D2).

The GetVizubiData(Data_Field;Report [;Field1;Item1 [;Field2;Item2 [;Field3;...]]]) function returns the value of a measure from a report using a combination of one or more dimensions (maximum 5). For example if you want to put the value of sales of a specific product in a cell in your report you can use this function.

Data_Field is the field that contains the measure to return.
Report is the name of the report to use.
Field1-Item1, etc…: the field name and value pair to use in the search. You must include a field-item pair for each dimension in the report.

(1) Click on the cell C4: the syntax of the Vizubi formula is as follows: = GetVizubiData("Sales","Sales by Product YM","Product Name",$A4,"Year-Month",C$1) where:
Data_Field is "Sales"
Report is "Sales by Product YM": this is a report that is in the ndb file.
Field1,Item1: are respectively Product Name and $A4.
Field2,Item2: are respectively Year-Month and C$1.

The data in the References worksheet are values used to define the row number in the Vizubi Functions. These are useful when you want to be able to copy the Vizubi functions in Excel to create top/bottom ten lists.

The twenty sparklines on the left of the dashboard are Excel graphs built using the series formula in Excel. The formula includes data from the Products Last 12 Months worksheet for the first 10 sparklines and the data from the Customers Last 12 Months worksheet.

If you need support or suggestion to personalize your dashboard, contact us at: support@vizubi.com