If you are unable to create a new account, please email support@bspsoftware.com

 

Need to calulate the difference between elements of same column in crosstab

Started by lrajana, 23 Jun 2009 10:11:28 AM

Previous topic - Next topic

lrajana

Hi,
I need to calculate the varaince(difference) of a measure between the elemnts of a Date coulmn which has 24 months data in a cross tab report for different customers.

I need the difference between at month level of 2 years.
Ex: Difference of 2007Jan and 2008Jan and so on..

The issue is since both the elements that i am trying to calculate the variance are in the same column I am not able to achive it.

Any idea how I can achieve?

Thank you.


kalyan_y

Hi, I tried the solution for this approach assuming Year granularity.
Steps
1) Take 2 queries.
2) In Query 1, have the data for each customer for the current year.
3) In Query 2, have the data for each customer for the previous year.
4) Take a join query(Q3) and create a inner join on the customer id.
5) Take a Customer Id from either Q1 or Q2 into Q3. Take another data item and have a static text which u want to display as column header.
6) Take a dataitem in Q3 and write the calculation as total([Query2].[Quantity] for [customerid]) - total([Query1].[Quantity] for [customerid]).
6) Drag Crosstab on to the report page and drag Customerid as rows and Dataitem with static text as columns and measure.
I'm nt able to attach the report specification i worked on using Go Sales and Retailers package.