A common reporting requirement is "How do I compare a rows value to another row?". For example compare one months value to another
months value? If I have a list that looks like this:

Can I compare one month to another? Can I see the growth from one month to the next?

Set Up

To run this exercise, we need a list that looks like the above. We can do this using the sample Great outdoors data module. To produce the above list perform thses steps.

In this exercise I will use simple list that looks like this: 

This is a simple list report using the Date and Revenue field found in the Great outdoors data module located in : Team content / Samples / By feature / Core / Data

For this exercise I want the Revenue for Month rather than individual days. I will change the many days for a month into a single day. I do this by editing the Date data item from this:

to this:

This makes my list look like:

I then sort my list by Month and format the data item to not show the day. 

The Challenge

Sometimes there is a need to compare values from different rows within a report. Take this simple report. How much has the revenue grown compared to last month?

Running Difference

The easiest solution is the Running Difference Function.

This will give:

Moving Total

If we want to see the previous months value on the same row, then we can use the moving total function. For example, we may want to do a percentage growth from one month to another.

This gives:

My use of the function totals 2 rows. In 2015 Feb this would total 2015 Feb + 2015 Jan. I then subtract this rows value.

    Previous Months Revenue = ( 2015 Feb + 2015 Jan ) - 2015 Feb

Once the previous months value is on the same row then we could do any kind of calculation needed to either see the variance or the percentage change. Once your eyes are opened to the technique, any kind of lag becomes easy. Could I use this technique to lag a value by two months rather than one?

Changing the Order

The above functions work if our dates are in ascending order. But what about if we want the latest data first? If we take our previous answer:

And change the reports sort order, to sort Month descending rather than ascending:

You can see the technique no longer works. Moving total, as the functions help states, sums the previous rows. And that is the previous rows as we see them on the output, not as they are logically. Hence, for 2018 Jun the previous month value is shown as 2018 Jul value rather than May.

Thankfully there is a way around this.

If I access the query that my list is using

I can set the presort options on Month

I must have Month sorted ascending for my Moving Total function to work, regardless of what the final output looks like. As it stands, the list is sorted descending and as the list is based on this query, the list sort will take precedence to the query sort. I am now going to use the advanced query engine to overcome this
issue. (Excuse the MS Paint work!)

1. Accessing the Queries within the report
2. I add a new query and rename it My New Query
3. I then base My New Query on Query1, I want to use the results of Query1 in My New Query

1. In My New Query
2. I add all the objects from Query1

Finally, I change the list from using Query1, to My New Query:

We can now see that the list is sorted descending, and the Previous Months Revenue is correct.

How does this work? Query 1 has the presort on Month ascending and the Running Total calculation. Within Query 1 the running difference is calculated using the ascending month resulting in the correct calculation.

The results from Query 1 are used in My New Query. This doesn’t do any calculations as the previous month has already been calculated. This query can be sorted descending without affecting the result set.

(A note: a simple lag function is available on multidimensional data sources. The above is what to do for a relational source)

#Cognos Analytics