Display Rolling Average in BAQ

[dr_dan (Dan Ramirez)]

I’m having a total brain fart kind of moment.
I am trying to create a BAQ that shows the year and month in columns starting from current month and going backward by part number. My fourth column should show the rolling 12 month average of sales for a given part number. Ideally, I’d end up with something like this. Any suggestions?

[hkeric.wci (Haso Keric)]

BAQ - How can I show null rows for a sum calculated field?

a left join will not magically create a July, you must have an order in July. You could start with the table FiscalPer thats assuming you have 12 periods, for us it wouldn’t work. Maybe Company Calendar or some other table that has Months, otherwise you will have to build one with FOR XML. Idea 1: [image] Idea 2: [image] Idea 3: MonthlySales from Epicor Demo Database [image] [image]

Recursive CTE BAQ - Help Needed

I took a quick look at your query then noped out because it’s way more complicated that I have time for. But if you are looking for a rolling total you can use windowing functions. Sum(table.Field) over (partition by Separator1Table.SeparatorField1 , Separator2Table.SeparatorField2 etc. order by Table.FieldtoSortBy) This will give you a running sum without have to try and use a CTE. Not sure if that’s what you are looking for, but it seems simpler than whatever you are trying to do.

@jkane has been rolling partitions since 1983

[dr_dan (Dan Ramirez)]

Thanks. I’ll check those out. It looks promising.

[jkane (John Kane)]

[dr_dan (Dan Ramirez)]

You absolutely nailed it. Once I got the Fiscal Period table idea, the other pieces fit together perfectly. This is going to make a beautiful chart in a dashboard.