I've built a viz along these lines before - here's the basic concept;
- Build your viz with the September forecast
- Export your worksheet - Worksheet > Export > Crosstab to Excel
- Note the export also contains the forecast, this is the data you want to re-ingest into your viz
- Create/update your viz with the October forecast
- Re-ingest your September forecast data that you created earlier as a dual-axis viz
Thank you for your quick reply, really appreciate that!
I have never worked with crosstabs so not sure I am doing it right but it seems though that if I have to work on the project level this does not quite work alright... some projects have number of months listed in different report periods. other projects listed in August were not listed in September or vice-versa.
I also need to be able to drill down by region or even project level in future and the actual file has over 13780168 cells.
Hope it makes sense and I dont sound too dumb
The issue of projects coming in and out will have to be dealt with as a business problem - Tableau just paints up what the data says. If you can answer the question 'how does your boss want the projects to be modelled in/out' then we can probably help you with that too.
If you are having any further issues, can you upload an example ? Its hard to understand which area you are having difficulty with without an example.
It seems that I found a way....
LOOKUP(SUM(([Forecast])),-12) ... it seems to work fine for now
Thank you again Peter!!! Really appreciate your time!!!