So, it is 2020. If you are like me, you are glued to various news sources looking at the Covid-19 case numbers.
One of things I was wondering about is how they put the 7 day average line into the charts. I started thinking about how I would do this in Cognos. I had elaborate calculations, multiple queries mapped out in my head. Then I remembered - there is a function to do this for me in Cognos. Thanks, Cognos. Much easier than what I was planning!
moving-average ( numeric_expression , numeric_expression [ at expression { , expression } ] [ <for-option> ] [ prefilter ] ) moving-average ( numeric_expression , numeric_expression [ <for-option> ] [ prefilter ] ) <for-option> ::= for expression { , expression }|for report|auto
Returns a moving average by row for a specified set of values of over a specified number of rows. The "<for-option>" defines the scope of the function. The "at" option defines the level of aggregation and can be used only in the context of relational datasources.
Example: moving-average ( Qty, 3 )
Result: For each row, returns the quantity and a moving average of the current row and the preceding two rows.
Here are the steps:
I decided to use the Canadian Covid-19 numbers since it is so easy to download a .csv of their daily counts.
If you click the CSV button, it will give you a .csv file with all the counts.
I uploaded it to Cognos and created a data module.
I built a report adding a column and line visualization (clustered combination):
I added date and numtoday (the daily count of new cases) to the column section.
Then I went into the Query section of the report and created a query calculation. You cannot add a query calculation directly to a measure section of a visualization (don't ask me why not). So I added this calculation to the query: moving-average( [numtoday], 7)
Before I can add it to my visualization, I have to set the Detail aggregation. I changed it to Calculated.
Once we have that created, we can add it to the line section of the visualization.
Now, two critical steps to making this work - you need to sort by date and you need to use one axis.
On both the sections of the visualization (line and column) - choose the date on the x-axis and choose sort ascending.
Select the visualization and in the section Axes in Properties change Use single axis to Yes.
