Try the following expression
Sum(Total <[Sales Month] [REA Value])
The set expressions will not work because the set filtering is performed before the chart is built and hos no knowledge of the dimension values.
If the above expression does not work, then it might be necessary to modify the data model. I suggest that you post a spreadsheet with some sample data.
Hope that helps
I think you could solve it if you join the tables with a new field: the concatenation of Sales Month,ProductID and UnitID.
So, first load the Forecast table adding a new field like this:
Table: LOAD ... Sales Month & '-' & ProductID & '-' & UnitID as %key ...
Then, load the second table (Realised Sales) by joining it with the previous table with the same combination of fields named %key too:
LEFT JOIN(Forecast Table) LOAD Sales Month & '-' & ProductID & '-' & UnitID as %key ReaValue FROM <Realised Sales data source>;
I think that should work.
tried to rebuild the app. Can't see where QV should be wrong, see attached exam app.
avoiding sync-tables isn't a bad idea, but it won't change anything in this case above.
Regards to all
tt.qvw 158.5 K
Thank you all very much for your responses!
The 'Total' from Jonathan solved a part of the problem, but I have now struck a new problem. I only wish to sum the next four months of the forecast, and it has proved to be quite difficult to add restrictions to the Sales Month after imposing the Total clause.
I have tried the following:
=If([Sales Month]>=AddMonths([Forecast Month],1) and [Sales Month]<=AddMonths([Forecast Month],4),SUM(Total <[Sales Month]> [REA Value]), 0)
The result looks very good when using a table containing the 'Sales month' as a dimension, but as soon as this is removed from the dimension list (to resemble a chart), it breaks down.
I will try to make an excel file and a test QV with test data.