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.