Hello,
I have a question. I would like to calculated ratio's from a database with balancesheets in a pivot-table. The data is as follows:
<tbody>
</tbody>
What I would like is to have a calculated field in a pivot table that calculates the ratio Cash / Short Term Debt, like this: This is what the result should be:
<tbody>
</tbody>
I have been able to create this pivot-table myself, but I had to use a work around. I added two columns to the data, like this:
<tbody>
</tbody>
Then I was able to use a calculated field: = Cash / Short term debt. The result is shown above.
However I am looking for a way to calculate this without changing the data set, because the real dataset is huge and I want to calculate many different ratio's. I need flexibility. I hope anyone has a solution .
I hope the problem definition is clear
Kind regards,
Martijn
I have a question. I would like to calculated ratio's from a database with balancesheets in a pivot-table. The data is as follows:
Company | Balance Sheet no | Description | Value | Year |
Apple | 3 | Cash | 25 | 2018 |
Apple | 4 | Inventories | 35 | 2018 |
Apple | 5 | Short Term debt | 75 | 2018 |
3 | Cash | 20 | 2018 | |
4 | Inventories | 21 | 2018 | |
5 | Short Term debt | 40 | 2018 | |
Apple | 3 | Cash | 100 | 2017 |
Apple | 4 | Inventories | 80 | 2017 |
Apple | 5 | Short Term debt | 50 | 2017 |
3 | Cash | 60 | 2017 | |
4 | Inventories | 18 | 2017 | |
5 | Short Term debt | 20 | 2017 |
<tbody>
</tbody>
What I would like is to have a calculated field in a pivot table that calculates the ratio Cash / Short Term Debt, like this: This is what the result should be:
company | 2017 | 2018 |
Apple | 2,000 | 0,333 |
3,000 | 0,500 |
<tbody>
</tbody>
I have been able to create this pivot-table myself, but I had to use a work around. I added two columns to the data, like this:
Company | Balance Sheet no | Description | Value | Year | Cash | Short Term Debt |
Apple | 3 | Cash | 25 | 2018 | 25 | |
Apple | 4 | Inventories | 35 | 2018 | ||
Apple | 5 | Short Term debt | 75 | 2018 | 75 | |
3 | Cash | 20 | 2018 | 20 | ||
4 | Inventories | 21 | 2018 | |||
5 | Short Term debt | 40 | 2018 | 40 | ||
Apple | 3 | Cash | 100 | 2017 | 100 | |
Apple | 4 | Inventories | 80 | 2017 | ||
Apple | 5 | Short Term debt | 50 | 2017 | 50 | |
3 | Cash | 60 | 2017 | 60 | ||
4 | Inventories | 18 | 2017 | |||
5 | Short Term debt | 20 | 2017 | 20 |
<tbody>
</tbody>
Then I was able to use a calculated field: = Cash / Short term debt. The result is shown above.
However I am looking for a way to calculate this without changing the data set, because the real dataset is huge and I want to calculate many different ratio's. I need flexibility. I hope anyone has a solution .
I hope the problem definition is clear
Kind regards,
Martijn