Include data from excel into dashboard

#1

Hi,

I was wondering if someone could help me with this…

I have my sales targets in an excel s/sheet by employee and by day and by month. The monthly target is the daily target * the number of working days in the month.

In my dashboard coming from my data cube coming from our system database I have my daily and monthly sales by employee but I want to show them against the relevant target.

Can I store this information in Dundas so I can incorporate it into my dashboard?

I also need to know the number of working days that have elapsed at any point in the month. For us now, there are 22 working days in May 2019 and 12 working days have elapsed.

Our projected sales for the month is calculated by the (sales month to date / working days elapsed ) * total number of working days in the month.

Note: we don’t work in Saturday’s and Sundays or on bank holidays. There would be invoices generated every working day.
I have always shown this in excel but I would like to show it in Dundas.

In summary: i need help with…

  1. incorporating data from excel into my dashboard created from our system database.
  2. identifying the number of working days that have elapsed at any point in the month so I can calculate the average sales per day.
  3. using the total number of working days in the month to calculate the projected sales for the month.

In excel I have a worksheet that has all the months in our financial year and the number of working days in each month.

I also have a sheet with every date in the financial year and what working day that is in the corresponding month. For example in my s/sheet the 17th May 2019 is the 12th working day, the 18th of May is also the 12th working day (as it is a Saturday). Monday the 20th will be the 13th working day.

I hope this makes sense!

TC

#2

You should be able to pull in the Excel sheet and the Database data into the same cube and join it, if you have a key like the year and month in both sources.
https://www.dundas.com/support/learning/documentation/cleanse-consolidate-modify-data/using-a-data-cube-to-join-tables

sample:
in Database columns employee, year, month, day, sales (note if you just have the date then parse out the year, month and day using database date functions into three columns.

Excel columns Year, Month, working_days, projected_sales

Then join this data by the year and Month columns.

This can also be done with year and (other time frame). but sounds like you only use month.

Personally I would rather just get the Excel data into the database and make that the place where people look it up and change it (many option there like website to view and change the data SharePoint can even do it). Then you can use SQL to join the data into one result and pull that into Dundas.

1 Like
#3

Thanks for that James!

I dont have quick access to the developers but I will ask them to add tables into the database to store that information.

In the meantime, is it possible to add in this information into the metric set / data cude? The only information things I need to know are:

How many working days there are in a month:
What working day of the month “today” is; for example today is the 20th May 2019 and this is the 13th working day.

So once I have this in dundas then I can divide the sales month to date by the number f working days that have elapsed, and then I can multiply this average by the number of working days in the month to get a projected sales figure. I only need it at a total level rather than at a user/sales person level.

Hope this makes sense.

Tommy

#4

oh I missed you need that today is the X working day.
Let your SQL devs know that too.

What you will need to do is have the excel file have all that and be able to join it to the database data (employee actual sales). Also in an excel sheet the today is the X work day of the month.
It is going to be a couple of joins, and not simple ones. if you do not have much experience with joins see if on of your SQL Devs can help you out.

1 Like
#5

I just happened to have a tool that i wrote that was able to help with very little modification.

Copy this SQL code into a Dundas BI Data Cube using a ‘Manual Query’ and point it to any real SQL Database. This code will generate dates for the next two years and i coded in all of the USA holidays for you. Once you have this in a Data Cube you can create a metric set on a dashboard and just do a SUM of the [Work Day] column to figure out how many works days between now and the current date. (filtering on the current date)

or join this against an existing data source as suggested above…

https://microsite.dundas.com/api/resource/staticresource/holidays.html

image
image.png1186×874 40.2 KB

2 Likes
#6

‘just happened to have’

I want a copy of all your ‘just happened to have’ folder!

2 Likes
#7

That is exactly whats I was saying he needs, just did not have a need for it yet to share like you did Jeff.

and yes i want that folder too.

2 Likes