Hi,
I would like to learn how to create a rolling averages within BAQ. This would be a very helpful in many kinds of business analysis that we are trying to do. I’ve been googling the SQL language to try to figure out how to get it to work within a BAQ, but I don’t where to put this in the BAQ or what needs to be adjusted for translation.
This is the site that I was using to try to figure it out.
They have listed a couple different SQL examples, which I listed below. Forgive my ignorance about SQL, I don’t know what type of SQL E-10 is using. I’m assuming it’s server because I’ve used the dateadd function as it’s show in that example.
My SQL
select signups.date, signups.count, avg(signups_past.count)
from signups
join signups as signups_past
on signups_past.date between signups.date - 6 and signups.date
group by 1, 2
or
Server SQL
select signups.date, signups.count, avg(signups_past.count)
from signups
join signups as signups_past
on signups_past.date
between dateadd(day, -6, signups.date) and signups.date
group by signups.date, signups.count
In previous attempts and the select statements, I ended up just created sub queries and used the filtering in those to get the different things I needed, but for a rolling average, I would need a subquery for each date. I could create a sub query to do something like past 7 days, 30 days 90 days. But that doesn’t get me something that I can graph out so that someone could start looking at trends, only current states.
Here’s what I have, but it won’t let me run it because it says possible unauthorized query. I tried putting the group by information in the advanced group by section of the display fields, but I’m sure I’m doing something wrong.
select
[dailytotal].[ShipHead_ShipDate] as [ShipHead_ShipDate],
(avg( dailytotal.Calculated_RevDay )) as [Calculated_Ave]
from (select
[ShipHead].[ShipDate] as [ShipHead_ShipDate],
(sum( OrderDtl.ExtPriceDtl )) as [Calculated_RevDay]
from Erp.OrderDtl as OrderDtl
cross join Erp.ShipHead as ShipHead
inner join Erp.ShipDtl as ShipDtl on
OrderDtl.Company = ShipDtl.Company
And
OrderDtl.OrderNum = ShipDtl.OrderNum
And
OrderDtl.OrderLine = ShipDtl.OrderLine
inner join Erp.ShipDtl as ShipDtl and
ShipHead.Company = ShipDtl.Company
And
ShipHead.PackNum = ShipDtl.PackNum
group by [ShipHead].[ShipDate]) as dailytotal
group by select dailytotal.ShipHead_ShipDate, dailytotal.Calculated_RevDay , avg(dailytotal_past.Calculated_RevDay)
from dailytotal
join dailytotal as dailytotal_past
on dailytotal.ShipHead_ShipDate
between dateadd(day, -6,dailytotal.ShipHead_ShipDate) and dailytotal.ShipHead_ShipDate
Group by dailytotal.ShipHead_ShipDate
Again, sorry for my ignorance in the topic, and thanks for any help.
Brandon