Crosstab and complex calculations

building reports webintelligence
1

One of the concerns from my clients is that crosstab has a lot of limitations.
Users may want to have some calculation that involve part of the data inside the crosstab.

For example, assume I have a crosstab (Product, Date)->Revenue
There are Products A, B, C. Product C is actually a collection of A,B
So user may want to compare the sales of A,B with the sales of C.

The user may have some calculations like below
(Revenue for Product A + Revenue for Product B)/(Revenue in Product C)

However, I think you cannot use crosstab to do that.
Are there any workarounds?

summoner :hong_kong: (BOB member since 2008-12-18)

2

To understand the Crosstab problem, I think screenshot might help us to understand it in detail…

aniketp :uk: (BOB member since 2007-10-05)

3

Let me give an example. Here is the structure of the crosstab

_______Product Name
Date-----Revenue

For example, it will show something like

___________ A | B | C
19/3/2009----30 50 40

So now the client need the calculation (A+B)/C=2, as shown below:

___________ A | B | C | Ratio
19/3/2009----30 50 40 2.0

The problem for a crosstab is that I cannot get a particular item from the crosstab. i.e. I want to retrieve revenue of Product A, Product B and Product C in order to have the calculation. So I am thinking a workaround

Thanks

summoner :hong_kong: (BOB member since 2008-12-18)

4

First you need to have a column at the left side … right?

For that select the intersection values and take sum or average any at the left side, so that one column gets inserted. And now try your basic formula (A+B)/C. Let me know if it works properly , If not what results it is giving…?

aniketp :uk: (BOB member since 2007-10-05)

5

Let me give an example (Please see the attachment)

Now the item 244023 is actually a collection of item 244059 and 756024
If I want to get the ratio, the correct result should be (170+12)/725

I have no idea how to retrieve columns in crosstab to have such the formula

Thanks
sample.PNG

summoner :hong_kong: (BOB member since 2008-12-18)

6

Try this:

=(Sum(If([Product Name]="244059" Or [Product Name]="756024";[Total Revenue])))/Sum(If([Product Name]="244023";[Total Revenue]))

I tested this at my end and it was working fine.
The original formula I used for my calculation is:

=(Sum(If([Year Month]="2008-10" Or [Year Month]="2008-11";[Revenue]))) /Sum(If([YearMonth]="2008-12";[Revenue]))

Where YearMonth is same as like Product Name. And Revenue is same as Total Revenue.

aniketp :uk: (BOB member since 2007-10-05)

7

Thanks. It works

Unfortunately is that I understand this approach, but not my client. Are there any other approaches?
For example, do some settings on the header…something like that

summoner :hong_kong: (BOB member since 2008-12-18)

8

I think writing a variable is only the solution to this, as cross tab has certain limiltations. So you cannot play around the structure, but you can play around the input and output context to get the right results. :slight_smile:

aniketp :uk: (BOB member since 2007-10-05)