ReportNet Cross Query calculation 1

[JeannetteC]

Hi there,

I wondered whether anyone can clear something up for me? I have created two queries on the same report. These are crosstab reports (months along the top and measures down the side).

Sales 1 2 3 4 5 Total
Gross Sales 10 15 12 15 10 62
Cost etc
Margin etc


Returns 1 2 3 4 5 Total
Value 10 25 11 10 5 61
Cost etc
Margin etc

I would like to do a calculation between these two queries (from two different tables - sales & returns) and arrive at the net figures for Sales in a third crosstab, with the same layout as the previous two.
Is this possible?

Thanks!
Jeannette

 

[DoubleD]

Why not just run the third report as its own, and do the calculations against the database?

Pain is stress leaving the body.

DoubleD

 

[JeannetteC]

Hmmm, I'll try and figure that out - tahnsk for that!

 

[JeannetteC]

Hi,

Just had an opportunity to look at this, I'm not sure that I can so this in the data base, because date is not the only filter on the two parent queries. They also look at several other tables (products, customers etc).

I had a go using a Tabular Set (using the except function - it says this is minus). But, it retursn zeros - possibly because I am subtracting the months from each other (?).

Any ideas?

Thanks
Jeannette

 

[JeannetteC]

Hi,

Well, I have amazed even myself! It's amazing what a bit of peace and quiet can allow you to do!

I'm not sure if it is the most elegant way to do it, but I created a union in a tabular set and converted the credit values to negative and eventually got it working.

Hoorah!
Jeannette