I have a problem. I need to create a simple bar chart using the data below - but the dimensions I want are 5 different rows of data. I want to know if and how it's possible to do.
FirstYearCostProfile
SecondYearCostProfile
ThirdYearCostProfile
FourthYearCostProfile
FifthYearCostProfile
0.2
0.7
0.1
0
0
0.1
0.7
0.2
0
0
1
0
0
0
0
0.1
0.3
0.4
0.2
0
0.05
0.1
0.6
0.2
0.05
1
0
0
0
0
0.05
0.75
0.2
0
0
0.3
0.7
0
0
0
0.3
0.7
0
0
0
0.3
0.7
0
0
0
0.3
0.7
0
0
0
0.25
0.75
0
0
0
0.2
0.7
0.1
0
0
The numbers represent the percentage of the budget spent in that particular year. So for example, the first line - 20% of the cost is spent in year one, 70% in year two etc.
I have the actual financial data in the same table. I just want to see if I can create a bar chart with each year (1-5) being a dimension and the length of the bar chart is the sum of the money used in years 1, 2, 3, etc. As I said, I do have the actual figures. I have a COST column with the overal amount. So I can multiply by the values in the fields above.
Thanks for your reply. Your solution has helped, however, I can't quite get it perfect. If I add the crosstable function in the loadscript, it performs the action on the whole table. The only way I have got it to work is by deleting the other fields and only having the qualifier field and the fields I want to transpose. Do you know how I would get around this. Have copied the list of fields below.
[SchemeMaster]:
CROSSTABLE(CostYear, Spend, 1) LOAD SchemeID, TotCapexWithAdj * FirstYearCostProfile as [Year 1], TotCapexWithAdj * SecondYearCostProfile as [Year 2], TotCapexWithAdj * ThirtYearCostProfile as [Year 3], TotCapexWithAdj * FourthYearCostProfile as [Year 4], TotCapexWithAdj * FifthYearCostProfile as [Year 5]
But I also need these 20+ other fields from the same above table. I'm not sure how to structure the syntax/scripting.
