Hello,
I have following system for updating contracts and their prices:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Portfolio[/TD]
[TD]Service[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Monthly cost (Period 1: 1.4.2016-30.3.2017)[/TD]
[TD]Monthly cost (Period 1: 1.4.2017-30.3.2018)[/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]Service 1
[/TD]
[TD]1.5.2016[/TD]
[TD][/TD]
[TD]100 [/TD]
[TD]200 [/TD]
[/TR]
[TR]
[TD]Portfolio 3[/TD]
[TD]Service 2[/TD]
[TD]1.7.2016[/TD]
[TD]1.8.2017[/TD]
[TD]300 [/TD]
[TD]200 [/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]Service 3[/TD]
[TD]1.7.2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]Service 4[/TD]
[TD]1.8.2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]10 [/TD]
[/TR]
[TR]
[TD]Portfolio 2[/TD]
[TD]Service 5[/TD]
[TD]1.9.2017[/TD]
[TD]1.10.2017[/TD]
[TD][/TD]
[TD]200 [/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]Service 6[/TD]
[TD]1.9.2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]400 [/TD]
[/TR]
</tbody>[/TABLE]
I want to see monthly and yearly total prices for portfolio. I have manual system already, where I update prices by hands. I have about two hundred contracts and their prices differ yearly. Biggest problem is that new contracts come and old ones retire all the time, but I need keep up whole system. System has to show monthly prices for any given month. I need also update contract-table all the time and add or modify contracts.
What I want to see for any month:
DECEMBER 2016
[TABLE="width: 500"]
<tbody>[TR]
[TD]Portfolio[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]100 [/TD]
[/TR]
[TR]
[TD]Portfolio 3[/TD]
[TD]300 [/TD]
[/TR]
</tbody>[/TABLE]
DECEMBER 2017
[TABLE="width: 500"]
<tbody>[TR]
[TD]Portfolio [/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]710 [/TD]
[/TR]
</tbody>[/TABLE]
I have tried to use PivotTable and connect different kind timetables to my contract table - without success. Do you have ideas what is the easiest solution for me? I was thinking to use Calculated Field/Item, but with multiple tables in PivotTable, this seems not to be possible. I want to create system, where to pick any month and then check portfolio prices.
Best regards
J
I have following system for updating contracts and their prices:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Portfolio[/TD]
[TD]Service[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Monthly cost (Period 1: 1.4.2016-30.3.2017)[/TD]
[TD]Monthly cost (Period 1: 1.4.2017-30.3.2018)[/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]Service 1
[/TD]
[TD]1.5.2016[/TD]
[TD][/TD]
[TD]100 [/TD]
[TD]200 [/TD]
[/TR]
[TR]
[TD]Portfolio 3[/TD]
[TD]Service 2[/TD]
[TD]1.7.2016[/TD]
[TD]1.8.2017[/TD]
[TD]300 [/TD]
[TD]200 [/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]Service 3[/TD]
[TD]1.7.2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]Service 4[/TD]
[TD]1.8.2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]10 [/TD]
[/TR]
[TR]
[TD]Portfolio 2[/TD]
[TD]Service 5[/TD]
[TD]1.9.2017[/TD]
[TD]1.10.2017[/TD]
[TD][/TD]
[TD]200 [/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]Service 6[/TD]
[TD]1.9.2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]400 [/TD]
[/TR]
</tbody>[/TABLE]
I want to see monthly and yearly total prices for portfolio. I have manual system already, where I update prices by hands. I have about two hundred contracts and their prices differ yearly. Biggest problem is that new contracts come and old ones retire all the time, but I need keep up whole system. System has to show monthly prices for any given month. I need also update contract-table all the time and add or modify contracts.
What I want to see for any month:
DECEMBER 2016
[TABLE="width: 500"]
<tbody>[TR]
[TD]Portfolio[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]100 [/TD]
[/TR]
[TR]
[TD]Portfolio 3[/TD]
[TD]300 [/TD]
[/TR]
</tbody>[/TABLE]
DECEMBER 2017
[TABLE="width: 500"]
<tbody>[TR]
[TD]Portfolio [/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Portfolio 1[/TD]
[TD]710 [/TD]
[/TR]
</tbody>[/TABLE]
I have tried to use PivotTable and connect different kind timetables to my contract table - without success. Do you have ideas what is the easiest solution for me? I was thinking to use Calculated Field/Item, but with multiple tables in PivotTable, this seems not to be possible. I want to create system, where to pick any month and then check portfolio prices.
Best regards
J