I have a power user who needs to generate a "complaint rate". He needs to be able to divide the number of service notifications received in a month by a 6 month moving average of units shipped. In this same query he needs to show the complaint rate for the past 6 months. I am not a query writer...I only know the backend. But I am at a loss of how to select 6 months of data upon a load to calculate the 6 month moving average of units shipped. Any suggestions/ideas would be greatly appreciated. (Or is this a calculation that can be created in the query?)
I think you can do this calculation in the query designer using offest on the month varible..
If the user wants to generate the report for july..you can have a variable where he will put in July and with having variable offsets and restricting the key figures by these you can calculate the avg over the previous 6 months..
Hope this gives you a fair idea..
I think this can be done in the Starting Routine of
the Upd rules.
If you are using ODS it will be easier.
for each day you need an avg of the past 6 months, so when ever you load a record you can go to the ODS it resides in via SQL and calculate the 6 months trailing avg.
to initialize the data you run a repair full load that will make the calculation.