How do they do that...7-day average line

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.

There you go. A 7-day average on visualization in Cognos.