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.