Sales crosstab report

[MrKlar]

Hi everyone,

I want to create a sales controlling report consisting of a crosstab. Unfortunately I get stuck at one point. I use relational data coming from a data module.

The crosstab is supposed to show the development of sales amount (EUR) over the months of the current period (a period starts from first of calendar year until the last month with data for the whole month - which at the moment would be Jan until Oct - but without the last full month (October).
Next to that (= to the right of the months columns) I need the measure for the last full month (Oct 2024).
Next to that I need the measure for that month one year prior (Oct 2023).
Next to that I need the difference / derivation in absolute numbers between Oct 2024 and Oct 2023.
Next to that I need that difference in percent.
Next to that I need the total (aggregated) sales for that period (= current period).
Next to that I need the total sales for the same period one year prior (= prior period).
Next to that I need the difference / derivation in absolute numbers between current and prior period.
Next to that I need that difference in percent.

Underneath the sales measure (= currency) I have numerous other measures like sales quantity, profit etc.

To me that sounds simple but I'm not able to get the results without using some - in my opinion not well designed - solutions where I hide columns with render variables, which can be messy in a crosstab, especially when nesting node members. I've also found a solution using dimensional functions but I think using these functions with relational data is not the best idea, either.


Here's a  sketch how it's supposed to look like:


                                                           2023                                      |            |             | Diff Oct24/ |              |Jan-Oct|Jan-Oct| Diff Jan-Oct    |
                                   Jan  Feb  Mar  Apr  May  Jun  Jul  Aug  Sep | Oct 24 | Oct 23 | Oct 23(abs) | Diff(%) | 2024    | 2023    | 2024/23 (abs) | Diff(%) |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
sales amount (EUR)    10   5      5       5      3      2      5     10    5     | 10       | 5           | +5               |+100% | 60       | 48         | 12                   | +25%
sales quantity              X    X      X    ...
...



I'm very thankful for your answers.

[dougp]

That doesn't sound simple at all.  And it's not necessarily a crosstab.

This will have 2 rows?  If not, more examples and information about how it gets summarized is critical.

Option 1:  Craft the query to compute the various "month" category values (column labels) and use a crosstab.  (so, you'll have 2 columns:  amount and quantity)
Option 2:  Pre-pivot the data into columns and compute values as needed.

Either way, it's not necessarily really difficult, just tedious.  But it's also very specific to your need.  There is no generic solution.

[MrKlar]

Thanks for the reply.

The crosstab will have 8 rows total. Some of them measures, some are calculated based on the measures values.

Option 1 wouldn't be sufficient for me. I want the user to select the start and end month individually, also across years. So I need the amount of node members (months) to be flexible.

I don't entierly get your idea behind Option 2. Is it meant in the same way as Option 1? What do you mean by "pre-pivot the data" ?

[dougp]

Pre-pivoting the data would look like this in SQL:

Code Select Expand

select
  <dimensions>
, sum(
    case
      when MyDimension = 'a' then MyMeasure
    end
  ) as 'A'
, sum(
    case
      when MyDimension = 'B' then MyMeasure
    end
  ) as 'B'
, <etc>
So, all of your measure values are pre-aggregated by the query into each respective bucket.  Calculations are handled as needed.  Each bucket becomes a column in a List visualization.
That won't work if you don't know how many columns you want in your output.

You need option 1.  You'll need to get creative.  Each group of columns comes from a query.  You'll build this in Cognos, but the SQL would look something like...

Query1
(months to date this year)

Code Select Expand

select
  <dimensions>
, d.MonthAbbrev
, cast(d.Year, varchar(4)) as 'Year'
, <measure>
from <tables>
  inner join MyDateTable d on d.DateID = fact.DateID
where d.Year = _year(_add_months(current_date, -1))
  and d.MonthNum <= _month(_add_months(current_date, -1))
Query2
(recent full month a year ago)

Code Select Expand

select
  <dimensions>
, d.MonthAbbrev
, cast(d.Year, varchar(4)) as 'Year'
, <measure>
from <tables>
  inner join MyDateTable d on d.DateID = fact.DateID
where d.Year = _year(_add_months(current_date, -1)) - 1
  and d.MonthNum = _month(_add_months(current_date, -1))

Query3
(difference between last month and same month a year ago)

Code Select Expand

select
  <dimensions>
, d.MonthAbbrev || cast(_year(_add_months(current_date, -1)) - 1, varchar(4)) || '(abs)' as 'MonthAbbrev'
, 'Diff ' || d.MonthAbbrev || cast(_year(_add_months(current_date, -1)), varchar(4)) || '/' as 'Year'
, abs(
    sum(
      case
        when d.Year = _year(_add_months(current_date, -1))
          then <measure>
      end
    ) -
    sum(
      case
        when d.Year = _year(_add_months(current_date, -1)) - 1
          then <measure>
      end
    )
  ) as 'MeasureName'
from <tables>
  inner join MyDateTable d on d.DateID = fact.DateID
where d.Year in (_year(_add_months(current_date, -1)) - 1, _year(_add_months(current_date, -1)))
  and d.MonthNum = _month(_add_months(current_date, -1))

...etc...

Query 9 is a UNION of all of the other queries and is the source query for the crosstab.

You'll probably need a ton of conditional formatting based on the column labels.

I'm also not sure how you'll handle mixing what I presume is 8 different measures into this.

Like I said -- Not simple.  You're basically using Cognos Analytics as an integrated development environment.