Interesting Problem with Year over Year Analysis

(Anthony Stark) #1

I’m creating a BAQ to deliver year over year sales data by Sales Rep and by Customer monthly and year to date. I’ve completed the entire BAQ but my issue is with showing year to date sales in a month where there was no sales. for example, if Customer X had $10 in sales in January and 5$ in sales in February, and $0 in sales in March, their march YTD sales should still show as $15 for that customer. However, that customer doesn’t show up at all since there is no March sales for that customer.

I first created an inner-query that gets all sales by customer.
I then created a second inner-query to get the total sales by month and YTD sales by month.
I then created another inner-query that sets all the FiscalYear values to FiscalYear - 1
I then joined the two tables on sales custID = custID, SalesRepID = SalesRepID, FiscalYear = CalculatedFiscalYear, and Fiscal Period = FiscalPeriod.

I’ve read suggestions to use CTEs to create a temporary table that consists of just the months ( 1 - 12 ) and then I can somehow take all of the customers that were sold to during the year and join them against each month row so that every customer shows up 12 times, then tie that back to the main query somehow. I’m just having a mental block and can’t picture how this would work in Epicor. IF anyone has any suggestions I’d appreciate it.

(John Mitchell) #2

I’ve had great luck when it comes to fixed time series grouping like by day, week, or month by pivoting the data. Here is a Youtube video that walks you through the process.

(Anthony Stark) #3

I watched the video, and it’s interesting and I wish I knew about that for an earlier project, but I don’t think it’s right for this situation since I’d like to have the month contained within the row itself.

I just created a CTE to display a column containing all 12 months and I’m going to try and link that to a result set of all customers for a given sales rep.

If anyone is curious:

  1. created a CTE subquery with a calculated column of type int with the value of 1
  2. created a UnionAll subquery containing the CTE subquery from step one, filtered for calculatedColumnMonth < 12 constant, containing a calcuated column of type int consisting of the code “CTEtable.CalculatedColumnMonth + 1”
  3. Created a final inner query consisting of the CTE subquery, with a calculated column of type in with the value “CTEtable1.CalculatedColumnMonth”

I don’t like doing this but I honestly don’t know how that all works but I’ll learn how at a later time I guess.

(Brandon Anderson) #4

Just a thought, but it might be easier to create the list of months by making a subquery that looks at all sales, by all customers, and just grabs the month (only, but maybe add year if you want) and groups by that. You end up with the list of months that have any sales in it. Theoretically it’s possible to miss a month, but unlikely if you include all customers. Then you have a list of months to join to the rest of your query.

(Anthony Stark) #5

I like that, especially if I filter it to just look at a specific year and maybe a specific customer I know had sales all year. I’ll have to test the difference in efficiency between the two methods.