BAQ 21 Day Sales Average

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

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:

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

Many Thanks


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.

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

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



You could also try

CASE WHEN … then … else end

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.

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

I changed it to this:

but it displays as 0