Sales Analysis Dashboard

Kinetic ERP
, ,
1

We are trying to create a sales analysis dashboard that displays the following:

  1. Profit – the summary by month with the headings of month, year, sales, cost, profit, and percent.
  2. Compare – this compares the current year with last year. The headings would be month, year, this year, last year, change, and percent.
  3. Customer – summary by customer with the headings of customer no., name, ytd sales, ytd cost, and percent.
  4. Salesman - summary by salesman with the headings of salesman no., name, ytd sales, ytd cost, and percent.
  5. Part – summary by part sold with the headings of part no., site, description, ytd sales, ytd cost and percent.
  6. Customer group – summary by code with the headings of group code, description, ytd sales, ytd cost and percent.
  7. Product group – summary by product group with the headings of group code, description, ytd sales, ytd cost and percent.
  8. State – summary by state with the headings of state, description, ytd sales, ytd cost and percent. This should be the ship to state and not the bill to state.
  9. Site – summary by month with the headings of month, year, sales, cost, profit and percent.
  10. Roll view – summary by month with the headings of month, year, this year, last year, change, and percent. This is a 12 month roll.

Any suggestions on how to begin and create this dashboard? Has anyone done anything similar to this before?

2

I would start with a good BAQ that includes all of the different groupings along with the dollars. I think I would start with the InvcDtl/InvcHead table. You may need to filter out things like stuff that isn’t posted, deferred revenue, etc. based on your company’s preferences. You should be able to join customer. Then from Customer you should be able to join to Salesman (alternatively you can get it off the Order), Customer Group, State, etc. You may need to play around with where you get the value as they often will exist in multiple places. One Time Shipments (OTS) may go to a different state than the customer, etc. Same thing with alternate bill-to locations. Once you get a nice BAQ that shows you all the granular details, you will copy that to a new one and then start grouping by the fields you want and aggregating the sales dollars fields. I am not sure if this is the best way, but it’s a way. You mentioned you wanted a dashboard, so this will allow you to have several related BAQs in a single dashboard. You can even pub/sub to the details BAQ if you’d like. When a discrepancy emerges in the summarized views, it will be handy to quickly look at the details to see what might be causing it. Just my two cents.

2 Likes
3

How do I get the value for total sales and total cost per month each year?

4

Also I added a calculated field for month — DatePart(mm,InvcHead.InvoiceDate)
How do I replace the 1,2,3… with January, February, March, etc.?

1 Like
5

You’ll check the Group By checkbox on the calculated month/year columns. Then you’ll create a calculated field that does some aggregate calculation like:

sum(InvcDtl.ExtPriceDtl)

Try using:

DateName(month, InvcHead.InvoiceDate)
2 Likes
6

I was able to get the Total Sales after linking the invcdtl table to invchead; and replace the 1,2,3 with Jan, Feb, March using the updated code you provided. Do you know how to get total cost? Profit and percent would just get calculated off of the prior data

7

There are cost columns in InvcDtl. You will need to add them all together and then take the sum of that sum (if that makes sense). Something like:

sum(InvcDtl.MaterialCost + InvcDtl.LaborCost + InvcDtl.SubContrCost + InvcDtl.BurdenCost + InvcDtl.MtlBurCost)

I’m not sure if those are the exact names but they will be something like that.

1 Like
8

For Total Sales, would I use InvcDtl.ExtPrice or InvcHead.InvoiceAmt? Or does it matter? Pricing just seems to be really low for each month when I run the query. I would have to double check if the field being used has 0’s more often than not

9

I don’t know for sure. I would audit the details like you said and see which one is reliable. ExtPrice is the one I’ve been using. But I also know that this then puts it in a context that doesn’t matter how it was paid for. I end up having to subtract Discount to get the Ext Net Price and then we also consider some Misc. Charges (but not others). You may find that there’s a better field that just does what you want rather than go through the extra calculations.

1 Like
10

Is there a way to make the months come in order Jan to Dec rather than going alphabetically?

11

I’m reliving this with you as I think I had to work through each of the things you brought up.

There’s a couple ways. I’d probably just leave the calculated field you had setup originally for DatePart(mm, InvcHead.InvoiceDate). Sort it by that one. But still keep the month name column also. You can hide the numeric version in your dashboard with very little effort and only expose the month name.

Alternatively, you could do something like:

case when DatePart(mm, InvcHead.InvoiceDate < 10 then '0' else '' end + DatePart(mm, InvcHead.InvoiceDate + '-' + left(DateName(month, InvcHead.InvoiceDate), 3)

That would give you something like 01-Jan, 02-Feb, 03-Mar, etc. You don’t have to use the left function. But I like it to be a little shorter. Then it would sort properly.

2 Likes
12

Is there a good way to pull ThisYear and LastYear so that it defaults to 2022 and 2021 and then next year it would look at 2023 and 2022?

13

Use your BAQ Special constants. There is one called “Year” that will give you the current year. Then you can just do this for LastYear in a calculated field:

Constants.Year - 1.
1 Like
14

That works. But what if I wanted 2 columns of let’s say the years 2022 and 2021 and then 2 more columns that pulls the sales from 2022 and 2021. Is there a way to group multiple times but display in the same table? These would be the headers of this table:

Month - ThisYear(Constants.Year) - ThisYearSales - LastYear(Constants.Year - 1) - LastYearSales

15

For calculated column ThisYearSales I would do a case statement that looks at the InvoiceDate.

sum(
case
  when DatePart(year, InvcHead.InvoiceDate) = Constants.Year
  then InvcDtl.ExtPrice
  else 0
end
)

For the Last Year column add a -1 to the end of the when clause (so you’re saying Constants.Year - 1).

2 Likes
16

What would I do if I wanted the LastYearSales column to be able to actually go back to previous years and just have a filter to select what year to look at? So all years not including current year

18

How would I link the Salesman to the Invoice tables to get all sales by Salesman?

19

It depends how your company does it. Do you have multiple salesmen per order? You can link the OrderDtl or OrderHed table to the InvcDtl table. Then it should give you visibility of the salesmen.

1 Like
20

Yes multiple salesmen at times. Let me give that a try

21

Would you happen to know the best way to pull the Ship To Address state abbreviation and full state name from the invoice?