HD0012 – How Do I Re-Create The Demographics Dashboard – 03

0
64

Please watch the video in 1080p because it is recorded in High Resolution.
Please note the script of the presentation is below so you can read along with the demonstration if you like.

Formulas For This Lesson


=IF(AgeBandPivot01!B4="","",AgeBandPivot01!B4)
=IF(AgeBandPivot01!B4="","",AgeBandPivot01!C4)
=IF(AgeBandPivot01!B4="","",AgeBandPivot01!D4)
=IF(AgeBandPivot01!B4="","",SUM($B$2:$B$30)/COUNT($B$2:$B$30))
=IF(AgeBandPivot01!B4="","",SUM($C$2:$C$30)/COUNT($C$2:$C$30))
=IF(AgeBandPivot01!B4="","",B2-D2)
=IF(AgeBandPivot01!B4="","",C2-E2)
=IF(AgeBandPivot01!B4="","",F2/D2)
=IF(AgeBandPivot01!B4="","",G2/E2)


=IF(IncomeBandPivot01!B4="","",IncomeBandPivot01!B4)
=IF(IncomeBandPivot01!B4="","",IncomeBandPivot01!C4)
=IF(IncomeBandPivot01!B4="","",IncomeBandPivot01!D4)
=IF(IncomeBandPivot01!B4="","",SUM($Q$2:$Q$30)/COUNT($Q$2:$Q$30))
=IF(IncomeBandPivot01!B4="","",SUM($R$2:$R$30)/COUNT($R$2:$R$30))
=IF(IncomeBandPivot01!B4="","",Q2-S2)
=IF(IncomeBandPivot01!B4="","",R2-T2)
=IF(IncomeBandPivot01!B4="","",U2/S2)
=IF(IncomeBandPivot01!B4="","",V2/T2)


=IF(LifeStagePivot01!B4="","",LifeStagePivot01!B4)
=IF(LifeStagePivot01!B4="","",LifeStagePivot01!C4)
=IF(LifeStagePivot01!B4="","",LifeStagePivot01!D4)
=IF(LifeStagePivot01!B4="","",SUM($B$51:$B$60)/COUNT($B$51:$B60))
=IF(LifeStagePivot01!B4="","",SUM($C$51:$C$60)/COUNT($C$51:$C60))
=IF(LifeStagePivot01!B4="","",B51-D51)
=IF(LifeStagePivot01!B4="","",C51-E51)
=IF(LifeStagePivot01!B4="","",F51/D51)
=IF(LifeStagePivot01!B4="","",G51/E51)


=IF(GenderPivot01!B4="","",GenderPivot01!B4)
=IF(GenderPivot01!B4="","",GenderPivot01!C4)
=IF(GenderPivot01!B4="","",GenderPivot01!D4)
=IF(GenderPivot01!B4="","",SUM($Q$51:$Q$60)/COUNT($Q$51:$Q$60))
=IF(GenderPivot01!B4="","",SUM($R$51:$R$60)/COUNT($R$51:$R$60))
=IF(GenderPivot01!B4="","",Q51-S51)
=IF(GenderPivot01!B4="","",R51-T51)
=IF(GenderPivot01!B4="","",U51/S51)
=IF(GenderPivot01!B4="","",V51/T51)


=IF(MaritalStatusPivot01!B4="","",MaritalStatusPivot01!B4)
=IF(MaritalStatusPivot01!B4="","",MaritalStatusPivot01!C4)
=IF(MaritalStatusPivot01!B4="","",MaritalStatusPivot01!D4)
=IF(MaritalStatusPivot01!B4="","",SUM($B$81:$B$90)/COUNT($B$81:$B90))
=IF(MaritalStatusPivot01!B4="","",SUM($C$81:$C$90)/COUNT($C$81:$C90))
=IF(MaritalStatusPivot01!B4="","",B81-D81)
=IF(MaritalStatusPivot01!B4="","",C81-E81)
=IF(MaritalStatusPivot01!B4="","",F81/D81)
=IF(MaritalStatusPivot01!B4="","",G81/E81)


=IF(NumberDependentsPivot01!B4="","",NumberDependentsPivot01!B4)
=IF(NumberDependentsPivot01!B4="","",NumberDependentsPivot01!C4)
=IF(NumberDependentsPivot01!B4="","",NumberDependentsPivot01!D4)
=IF(NumberDependentsPivot01!B4="","",SUM($Q$81:$Q$91)/COUNT($Q$81:$Q$91))
=IF(NumberDependentsPivot01!B4="","",SUM($R$81:$R$91)/COUNT($R$81:$R$91))
=IF(NumberDependentsPivot01!B4="","",Q81-S81)
=IF(NumberDependentsPivot01!B4="","",R81-T81)
=IF(NumberDependentsPivot01!B4="","",U81/S81)
=IF(NumberDependentsPivot01!B4="","",V81/T81)


AgeBandChart01AgeValues =OFFSET(ExcelTables!$A$2,,,COUNT(ExcelTables!$C$2:$C$30))
AgeBandChart01UnitsVarPctValues =OFFSET(ExcelTables!$H$2,,,COUNT(ExcelTables!$H$2:$H$30))
AgeBandChart01RevVarPctValues =OFFSET(ExcelTables!$I$2,,,COUNT(ExcelTables!$I$2:$I$30))
AgeBandChart02UnitsVar =OFFSET(ExcelTables!$F$2,,,COUNT(ExcelTables!$F$2:$F$30))
AgeBandChart03RevenueVar =OFFSET(ExcelTables!$G$2,,,COUNT(ExcelTables!$G$2:$G$30))


GenderChart01GenderValues =OFFSET(ExcelTables!$P$51,,,COUNT(ExcelTables!$W$51:$W$60))
GenderChart01UnitsVarPctValues =OFFSET(ExcelTables!$W$51,,,COUNT(ExcelTables!$W$51:$W$60))
GenderChart01RevVarPctValues =OFFSET(ExcelTables!$X$51,,,COUNT(ExcelTables!$X$51:$X$60))
GenderChart02UnitsVar =OFFSET(ExcelTables!$U$51,,,COUNT(ExcelTables!$U$51:$U$60))
GenderChart03RevenueVar =OFFSET(ExcelTables!$V$51,,,COUNT(ExcelTables!$V$51:$V$60))


IncomeBandChart01IncomeValues =OFFSET(ExcelTables!$P$2,,,COUNT(ExcelTables!$W$2:$W$30))
IncomeBandChart01UnitsVarPctValues =OFFSET(ExcelTables!$W$2,,,COUNT(ExcelTables!$W$2:$W$30))
IncomeBandChart01RevVarPctValues =OFFSET(ExcelTables!$X$2,,,COUNT(ExcelTables!$X$2:$X$30))
IncomeBandChart02UnitsVar =OFFSET(ExcelTables!$U$2,,,COUNT(ExcelTables!$U$2:$U$30))
IncomeBandChart03RevenueVar =OFFSET(ExcelTables!$V$2,,,COUNT(ExcelTables!$V$2:$V$30))


LifeStageChart01LSValues =OFFSET(ExcelTables!$A$51,,,COUNT(ExcelTables!$C$51:$C$60))
LifeStageChart01UnitsVarPctValues =OFFSET(ExcelTables!$H$51,,,COUNT(ExcelTables!$I$51:$I$60))
LifeStageChart01RevVarPctValues =OFFSET(ExcelTables!$I$51,,,COUNT(ExcelTables!$I$51:$I$60))
LifeStageChart02UnitsVar =OFFSET(ExcelTables!$F$51,,,COUNT(ExcelTables!$G$51:$G$60))
LifeStageChart03RevenueVar =OFFSET(ExcelTables!$G$51,,,COUNT(ExcelTables!$G$51:$G$60))


MaritalStatusChart01MSValues =OFFSET(ExcelTables!$A$81,,,COUNT(ExcelTables!$C$81:$C$90))
MaritalStatusChart01UnitsVarPctValues =OFFSET(ExcelTables!$H$81,,,COUNT(ExcelTables!$H$81:$H$90))
MaritalStatusChart01RevVarPctValues =OFFSET(ExcelTables!$I$81,,,COUNT(ExcelTables!$I$81:$I$90))
MaritalStatusChart02UnitsVar =OFFSET(ExcelTables!$F$81,,,COUNT(ExcelTables!$F$81:$F$90))
MaritalStatusChart03RevenueVar =OFFSET(ExcelTables!$G$81,,,COUNT(ExcelTables!$G$81:$G$90))


NumberDependentsChart01NDValues =OFFSET(ExcelTables!$P$81,,,COUNT(ExcelTables!$W$81:$W$91))
NumberDependentsChart01UnitsVarPctValues =OFFSET(ExcelTables!$W$81,,,COUNT(ExcelTables!$W$81:$W$91))
NumberDependentsChart01RevVarPctValues =OFFSET(ExcelTables!$X$81,,,COUNT(ExcelTables!$X$81:$X$91))
NumberDependentsChart02UnitsVar =OFFSET(ExcelTables!$U$81,,,COUNT(ExcelTables!$U$81:$U$91))
NumberDependentsChart03RevenueVar =OFFSET(ExcelTables!$V$81,,,COUNT(ExcelTables!$V$81:$V$91))


ExcelTables!AgeBandChart01AgeValues
ExcelTables!AgeBandChart01UnitsVarPctValues
ExcelTables!AgeBandChart01RevVarPctValues
ExcelTables!AgeBandChart02UnitsVar
ExcelTables!AgeBandChart03RevenueVar


ExcelTables!GenderChart01GenderValues
ExcelTables!GenderChart01UnitsVarPctValues
ExcelTables!GenderChart01RevVarPctValues
ExcelTables!GenderChart02UnitsVar
ExcelTables!GenderChart03RevenueVar


ExcelTables!IncomeBandChart01IncomeValues
ExcelTables!IncomeBandChart01UnitsVarPctValues
ExcelTables!IncomeBandChart01RevVarPctValues
ExcelTables!IncomeBandChart02UnitsVar
ExcelTables!IncomeBandChart03RevenueVar


ExcelTables!LifeStageChart01LSValues
ExcelTables!LifeStageChart01UnitsVarPctValues
ExcelTables!LifeStageChart01RevVarPctValues
ExcelTables!LifeStageChart02UnitsVar
ExcelTables!LifeStageChart03RevenueVar


ExcelTables!MaritalStatusChart01MSValues
ExcelTables!MaritalStatusChart01UnitsVarPctValues
ExcelTables!MaritalStatusChart01RevVarPctValues
ExcelTables!MaritalStatusChart02UnitsVar
ExcelTables!MaritalStatusChart03RevenueVar


ExcelTables!NumberDependentsChart01NDValues
ExcelTables!NumberDependentsChart01UnitsVarPctValues
ExcelTables!NumberDependentsChart01RevVarPctValues
ExcelTables!NumberDependentsChart02UnitsVar
ExcelTables!NumberDependentsChart03RevenueVar


Hello and welcome back to the next exciting installment of our Meta5 training video.

I am very pleased to see you come back to watch the next portion of our video training.

I want to say thank you very much for evaluating Meta5.

I want to say thank you very much for your time and attention.

Thank you.

In the prior two videos you completed the tasks of.

One.

Collecting all the data needed for the mini fact tables, and mini dimension tables, that will be used in the power pivot model.

Two.

Sending that data in to an Excel spreadsheet, using the Meta5 feature of sending data to Excel regions.

Of course, you could not start from the point of accessing data from the actual database.

This is because the actual database is a customer database.

And I mentioned to you that we generated all input data using a random number generator.

Please be assured that the real dashboards for the real customer show normal trends in the demographics of buyers of products.

I am sure you will remember where you were up to.

You can review the prior videos if you would like.

You have now completed almost all the Meta5 related tasks.

The only Meta5 task left is to enable the automation of the running of the capsule.

Almost everything you will see in the remainder of this series is Excel work.

Ok?

Now, you are going to create the Excel spreadsheet, that displays the data, that has been collected in the data spreadsheet.

You will see me highlight the finished capsule in the video.

You will see the spreadsheet you are about to create.

I would take this opportunity to remind you, that you should regularly save your work, by saving the spreadsheet, and closing Excel.

Meta five only saves the spreadsheet, in the meta 5 file system, when you close Excel, not when you press save in Excel .

And, laugh out loud, you can guess how much work I lost to find that out .

So please save your work, exit Excel, and reopen the spreadsheet regularly.

To make sure you see every step of the process, we are going to start with a blank spreadsheet.

So, you know the routine by now.

You go and get a blank spreadsheet out of blank icons .

Now, you name the spreadsheet as demonstrated on the video.

Now, open the new spreadsheet.

To get the data from the data spreadsheet, into the power pivot, in the dashboard spreadsheet, you need to click on data in the menu bar.

Now, click on manage data model.

Now, you should be inside power pivot for Excel .

Now, you click on, get external data, from other sources.

It will bring up a table import wizard.

Scroll down to the bottom of the table import wizard window, where you will see, text files, Excel file.

You select, Excel file, then click, next.

It will bring up a window, where there is a place to enter, Excel file path.

Now, click on the checkbox, use first row as column headers.

You click on browse, and navigate to the folder, and the spreadsheet, that contains the data collected by the meta 5 capsule .

You select the spreadsheet with the data in it.

Now, click on open.

Then you will come back to the table import wizard .

Now, click on test connection, to make sure the connection is working OK.

Now, click on next.

A window will open up.

It will show you the tables that are inside the data workbook.

Please note, when you make these kinds of connections, the file name is not able to be passed as a variable.

So, when you are running capsules iteratively, you must make sure that two capsules are not trying to use the same file, at the same time.

You will notice there are source tables, which end in the dollar sign.

These are the names of the worksheets .

The Excel region names do not have the dollar sign at the end of them .

You want to select the source tables, that are the Excel regions, and not the worksheet tables.

So now, select each of the source tables, that do not have the dollar sign at the end.

Now, click Finish .

Excel will import the regions into the power pivot model automatically .

You will see it do that .

Now you can see why you must set the headings in separate worksheets inside meta 5 .

Excel will use the headings you defined in the meta 5 capsule, as the column names, in the power pivot model.

So you want to take care, and be precise in the field names you use, in the headings spreadsheets, inside the meta 5 capsules .

In our example, we have not been so precise, because this spreadsheet was a prototype.

In the future, we will develop spreadsheets, with much more carefully defined heading names.

When we developed this spreadsheet, we did not realize the column names could not be easily changed in the Excel spreadsheet.

Now, you should see that Excel has imported all the regions successfully, into the power pivot model.

Now, click on the close button .

You can see along the bottom of the video, that all the regions have been successfully imported, in to the power pivot model.

Everything we teach you in the demonstration, from this point forward, is pure Excel.

Meta five is being used as the tool, to get your data into the power pivot model, to be used in your dashboards and reports.

It is being presented, as an alternative way, to get data into the Excel power pivot model.

Meta five can be run iteratively during the daily ETL batch processing .

You can even provide parameters, so people get customized spreadsheets, with only the data they need inside their spreadsheets.

Of course, because we are using Excel, the normal limitations of Excel apply.

Now on the month dim table, you want to sort month name description, by month in year .

Now, select month name description.

Now, click sort by column .

Now, in the sort by column dialog box, select month in year, in the sort by column .

You must repeat the process for month name.

In the life stage data, you must sort life stage short description, by life stage code .

So please follow along with the video.

Please note that this dashboard was a prototype.

When done properly, the dimension regions should have the dimension table keys in them, and they should link to the mini fact tables .

Please remember to do that if you build real dashboards.

Now, you should click on diagram view, because you need to link the mini dimension tables, to the mini fact tables.

We will demonstrate to you exactly how to do that in the video.

The reason for all the connections is this.

When we place slicers on the mini dimension tables, a click on a slicer will create a query, to select just the data from the mini fact tables, that is desired by the user, as expressed by the slicer click.

In this way, slicer clicks on the dimension tables, make the Excel spreadsheet be responsive, to slicer clicks, for all the data returned from all mini fact tables .

Now, you need to create a whole bunch of pivot tables, to select data out of the power pivot model.

On the video, you will see me create all the pivot tables necessary.

So, please follow along with the video very closely.

Please do everything you see on the video, on your desktop .

Now, you have all your pivot tables properly created.

Now, you will create a new worksheet, and you will call it Excel Tables .

Inside this new worksheet, you are going to define six new region names .

These six region names, will be used to perform calculations, to place data on to the charts.

So, you need to follow along very closely with what you see on the screen, and copy it to your spreadsheet, on your desktop.

Where you see there are a lot of formulas to cut and paste into the spreadsheet, you might want to copy them from the blog post, or from the example spreadsheet provided.

The next portion of the training, is to prepare formulas, that will form data series, that will be placed into the Excel charts .

Now, we are going to teach you a little trick about how to calculate those formulas .

We put the name of the formula, under each table, and then we calculate the formula, next to that name .

We do this for all the formulas, to make sure we have precisely the correct format, for each formula we need.

We are going to do this for you in this demonstration.

Please follow along on the video closely, and copy this on your desktop.

You should know, this is the best idea we have come up with to do this.

We believe this is very good practice for you.

If you would like to take the short cut, we have also provided the text for the final formulas, inside the demonstration workbook.

We have also provided the text for these formulas on the blog post.

So, you can cut some time from your training, if you would like to cut and paste the finished formulas, in to your workbook.

When you are putting the names into the name manager, you must be very careful to drop the AT sign.

When we were preparing this video, we forgot to drop the AT sign.

This is a very subtle mistake, because the symptom of the mistake is just an invalid formula message .

It is very hard to find this mistake.

To make sure you do not make the same mistake, we have provided you the formulas on the blog post.

So you can easily go and copy the formulas from the blog post .

Now we will show you the video, of cutting, and pasting, all of the formulas, into the name manager, ready to use in charts.

Please be careful to copy the actions on the video exactly.

This concludes this portion of the training.

I hope you liked it.

Thank you very much for listening.

I really appreciate your time and your attention.

I will see you back, in the next exciting installment, of using Meta5 to make building Excel dashboards, faster, better, and cheaper.

Ok?

I wish you a good day.