Total Annual Usage

cpelowski · January 20, 2020, 9:05pm

I have a BAQ which is utilizing the Part Table to pull in required data and then a subquery using the PartTran table to pull in the last tran date.
I also need the last years total usage in the report. I tried a calculated field where Part tran date >= @today - 365 days then sum PartTran.TranQty, but I just get the last tran quantity and not the total for the year.
What would be a suggestion to try?
Any help is appreciated.
Thanks
Carol

ckrusen · January 20, 2020, 9:22pm

Add a second sub-query for the totals, with the date criteria on the PartTran in this 2nd sub-query.

Don’t forget that you’ll have to account for the sign, based on the TranType. For example, Normal PUR-STK and STK-MTL parttran records will have a positive qty. PUR-STK obviously increases QOH, while STK-MTL decreases it. Lots of posts on here that list the sign for each TranType.

cpelowski · January 21, 2020, 9:44pm

Yay … that worked.

Thank you!!!

cpelowski · January 21, 2020, 9:45pm

Anya,

Thanks for responding… I was able to get it to work with a 2^nd Part Tran table.

Carol