Calculating % of total volume sales against forecast

[stevekoz]

Got another problem with % difference calculation. Report is in cross tab format.

I've included a copy of report in excel format - i've included in Col D the relevant example calculation i want to create. but at present i cannot get the logic to work. Keeps returning numerous errors. I'm working on a relational database not DMR/Cube.

I've pulled in a relevant query calculation adjacent to total sales. I've tried using value (tuple) but that won't work as its not DMR/Cube i believe.  I've tried using Total but i can't get that to work either.

Can anyone help suggesting how i can get round the problem. I simply cannot get the calculations i'm trying to work so any suggested expressions would be helpful.

[blom0344]

With an relational source our standard practice is to use unions when we need to perform calculations. This way the calculation outcome is just another set within the union that is displayed as a column (or row) in the crosstab. All queries use a 'type' dataitem storing a value for sorting and identifying the column header:

example (sql):

SELECT
'1. Forecast' as Type,..........................from .................
union all
'2. Calculation' as Type,..........................from .................
union all
'3. Total Sales' as Type,..........................from .................
....
....

[stevekoz]

Unions isn't something i've used before. I kind of know some of the theory to create the union.  I need to from the explorer bar, pull in query to the work area and Union adjacent to it. Then create a query for each of the two drop zones that appear yes? For each query i need to double click them and add the relevant data items. I can get that far -

Query - Union - Query 2 - 'Forecast' Data Item
                         Query 3 - 'Tonnes Sold' Data Item

Do i then need a 'Query 4' in order to populate the calculation?

No idea if i'm anywhere near the right track - i imagine not.

Thanks for the help though in advance.

[barrysaab]

Stevekoz,have you been able to resolve it?Could you please post your final solution.Thanks