Difference Total in Cross Tab

[#1]

We are having trouble getting the difference of two columns totals in a cross tab in Web Intelligence. We have a report based on an Excel spreadsheet which pulls in all of the data. There is a report filter that restricts the data to just two years. If the filter is on the block we are having the same issue.

The problem is that we want to see the difference in total sales revenue from one year to the next. I have defined the Sales Revenue Change variable as follows...

=Last([Sales revenue]) - First([Sales revenue])

That works fine for each State, but not the difference between the totals for 2006 and 2005 which should be $1,826,896.80.

Any suggestions on how to do this?

We are on 4.1 SP06 Patch 4.

Thanks,

Noel

[former_member629544]

Hi All,

I found a Solution for this issue. We need to use the "For All" function in the total row and play with the correct dimention.

In my case the correct total that I needed was the one linked to the gender for each First and Last column:

=Last([Sales)] ForAll ([Gender ])) - First([Sales] ForAll ([Gender]))

Best Regards,

Elena

[Former Member]

Hi Noel,

Can you share the structure of the report? Are the years hardcoded or coming from variable?

Regards,

Yuvraj

[Former Member]

Hello Noel,

Could you please try Sum([2006 Sales Revenue])-Sum([2005 Sales Revenue])?

I tested this using excel and its working fine for me please see below screen shot.

Regards

Niraj

[Former Member]

Hi Noel,

If you see the cell which you are getting error, it is doing the sum of the values column 2005 and 2006.

So try to put the formula =Last([Sales revenue]) - First([Sales revenue])

even in the field where you are getting error

[nscheaffer]

Sorry about that. I did as you suggested. The value is different, but still not correct.

Thanks,

Noel

[Former Member]

Hi Noel,

what was the 1st formula which you on the issue cell and gave the result 28291399 ?

What is the formula at the total column for the year 2005 and 2006?

and what value you are getting if you deduct those formulas?

what value you are getting if you directly do sum on third column?

What filters you have at the block level? is it only on year to get 2005 and 2006 data?

Did you try Sum([Sales revenue] where [year]=2006) - Sum([Sales revenue] where [year]=2005)

Because ideally the summation on the third column should give the correct result

[nscheaffer]

The $28,291,388.80 was the total Sales Revenue, not the difference. The filters are at the report level, but we get the same results if they are at the block leve.

I have tried what you suggested and it works, but we want it to be dynamic (no hard coded years).

We did get it to work with these variables...

Last Year=Year(CurrentDate())-1

Two Years Ago=Year(CurrentDate())-2

Change Total=[Sales revenue] Where ([Year] = [Last Year]) - [Sales revenue] Where ([Year] = [Two Years Ago])

I would like it to work using Min() and Max() to determine what was Last Year and Two Years Ago, but what we have is sufficient. Time to move on to something else.

Thanks!