Sales Dashboard

App Building
1

I have customer and sales data stored in a sql table. I have a select box with (week, month, quarter, year).

  1. First I want to limit customer count based upon the select box selection.
  2. Second I want to show month, quarter and yearly sales total in three components. Should I write three separate sql queries or is better to write one query and then use JavaScript to get the different values from a single sql query?

Current Customer Count SQL Query
select count(customerId)
from Customer
Where SalespersonUserId = {{localStorage.values.UserId}}

Sales Query
select WorkOrder.contractamounttotal as sold, WorkOrder.CreatedDate
From WorkOrder
Left Join Property on WorkOrder.PropertyId = Property.PropertyId
Left Join Customer on Property.CustomerId = Customer.CustomerId
Where WorkOrder.contractamounttotal > 0
AND Customer.SalespersonUserId = {{localStorage.values.UserId}}
Group By workOrder.contractamounttotal, WorkOrder.CreatedDate
Order By WorkOrder.CreatedDate Desc

1 Like
2

I would write one Query JSON with SQL using moment() against the the Sales Query
This isn't mine but it was posted in the forum and will give you an idea regarding the ability get quarter, month, year, etc...
Timeframe.json (8.7 KB)
You can then use the start and end dates as needed in each component

1 Like
3

Thanks! that really helped.

I'm sure I'm not doing this the best way but everything is right, I just can't see to figure out how to get the labels correct.

image
image1265×1075 141 KB

4

Plotly expects an array of labels that are matched to your data based on each one's index. You could do this by just passing ['new', 'old'] in the correct order to that field but likely a better pattern to use something like SELECT 'new' AS label, SUM(COUNT(... in your query, or better yet, specifically reference a field in the respective table that can properly classify it. With that, you'd be able to pass something like {{ getCustomerOldNew.data.label }} to the label field!

Does that work?

5

I was able to accomplish it with the below.

SELECT 'New' as Type, SUM(COUNT(customerId)) OVER() AS Total
FROM Customer
Where SalespersonUserId = {{localStorage.values.UserId}}
AND DATEDIFF(day, createdDate, GETDATE()) between 0 and 30

UNION
SELECT 'Old', SUM(COUNT(customerId)) OVER() AS Total
/*SUM(COUNT(customerId)) OVER() AS 'Old' */
FROM Customer
Where SalespersonUserId = {{localStorage.values.UserId}}
AND DATEDIFF(day, createdDate, GETDATE()) >30

1 Like