I need to create a column chart that show the 12-month Moving Average Sale for each of the past 13 months. I create a query that pull in the invoice data to calculate the monthly sale figure for the past 24 months. The 12-month Moving Average is calculated using this formula: moving-average ([Sale], 12). So the query returns 24 records, each with YYYYMM, Sale, Moving Average sale. How do I display just the last 13 months on the chart? I tried adding a Detail Filter on YYYYMM for the past 13 months but that cause the Moving Average calculation to be incorrect. Changing the Before/After Aggregation setting on the filter does not help. Any ideas on how I can solve this is great appreciated!
you leave your query as it is, and create a query reference to it.
In RS, go to te query explorer, click on queries. drag in a new query object. then, drag your existing query to the right of the new query and let go. The result should look like the attached. Now, in your new query, you can filter on just rolling 13.
I tried that, but it doesn't work. The moving average is still wrong.
That makes no sense - if the averages are wrong in your referencing query, then they are wrong in your referenced query, as the referencing query does not do any calculations; it just filters on rows and passes through the value.
Try and project both your queries in lists next to each other and see if you can understand what's going on.
I'm not sure what went wrong, but it works after I delete and re-create the query from scratch. A Cognos bug?
Thanks hespora for all your help!
sure thing, glad I could help! :)