BAQ 21 Day Sales Average

Epicor ERP 10
1

Hi Everyone

Hope your all settling into 2020 well!! Im still writing 2019 in my reports!

I want to remove the requirement for using excel to work out a 21 day sales average, and ultimately a 21 day, 3 month and 1 year averages.

I have the start of the baq - which sums all orders grouped by date to give a total for each day

image
image223×544 5.14 KB

I want to then take each days Total Daily sales add the next 21 days Total daily sales and divide by 21 to give the average.

This seems like an impossibility to me in a baq - something easily achieved in excel. As you can see below :+1:

image
image395×621 19 KB

Can anyone offer me some guidance on how to achieve this ?

Many Thanks

Carla

2

Hi Carla,

The easiest way I can think of is to create three different calculated fields. They would look like:

If (salesDate <= your_days then salesAmt else 0)

where your days is 21, 3 months, or one year. Do this in a subquery then sum them up in the Main query.

Mark W.

3

Hi Carla,
have you checked this thread ? i think that you can use the same principle to calculate your running average instead if summation.

4

Thanks Mark- I tried your advice like below but it doesn’t compile

image

image

5

You could also try

CASE WHEN … then … else end

6

Sorry Carla, I didn’t give the exact syntax but was just giving a strategy to get there. I like @aclements suggestion for using a case statement. Also, make sure your ranges do not overlap. You may want to use weeks to make it easier. Zero to three weeks; four weeks to 12 weeks and 13 weeks to 52 weeks.

7

Thankyou- could you expand a little on this solution im not entirely sure …

8

I changed it to this:

image
image731×441 15.6 KB

but it displays as 0

image