Kaushik Joshi
Member
Hi all Dashboard Enthusiasts / Experts - Friends,
Have a challenging Database cum Dashboard problem....will try my best to explain....
I DO NOT EXPECT A COMPLETE SOLUTION......
Any type of Conceptual help like giving Model formulas that can be adopted or Ideas to create a better data model will really help.....
I need to create a Data Model and Reports based on following facts.
(I am also attaching a file showing some basic work I have done.... I am not able to figure out How am I going to track 60 to 100 parties with each having 1 or 2 or 3 ....up to 6 different types of transactions )
My company name is X.
X belongs to a group named SG.
SG has other companies named A, B, C, D, E……..and so on.
X enters into 5 or 6 types of transactions with each of the Companies A, B, C, D, E…….
Suppose transaction types are T1, T2, T3, T4, T5, T6.
Amount of transactions X can enter with other related companies is limited by approvals provided by a Legal Committee that monitors such transactions..
I want to track the following metrics:
a) Approvals taken against each Party & Transaction type combination for any given quarter:
Opening Balance, Consumption, Closing Balance
Closing Balance of Q1 will be the opening Balance of Q2
b) Depending on Comparison of Approval Amount with Transaction Amounts in that period, we need to track if the approval is
insufficient then highlight the same where approvals are less than Transactions
OK if no more approval is required ( Approval = transaction )
Excess available where approvals are greater than Transactions
c) At the end of a period of year which is 1st September to 31st August, in case there is any excess approval, the same will get lapsed
d) Based on trend of Transactions and Approvals we should be able to take Approvals in advance
Hope I have not made it too complex......in fact it is a complex problem that needs to be simplified !!
Have a challenging Database cum Dashboard problem....will try my best to explain....
I DO NOT EXPECT A COMPLETE SOLUTION......
Any type of Conceptual help like giving Model formulas that can be adopted or Ideas to create a better data model will really help.....
I need to create a Data Model and Reports based on following facts.
(I am also attaching a file showing some basic work I have done.... I am not able to figure out How am I going to track 60 to 100 parties with each having 1 or 2 or 3 ....up to 6 different types of transactions )
My company name is X.
X belongs to a group named SG.
SG has other companies named A, B, C, D, E……..and so on.
X enters into 5 or 6 types of transactions with each of the Companies A, B, C, D, E…….
Suppose transaction types are T1, T2, T3, T4, T5, T6.
Amount of transactions X can enter with other related companies is limited by approvals provided by a Legal Committee that monitors such transactions..
I want to track the following metrics:
a) Approvals taken against each Party & Transaction type combination for any given quarter:
Opening Balance, Consumption, Closing Balance
Closing Balance of Q1 will be the opening Balance of Q2
b) Depending on Comparison of Approval Amount with Transaction Amounts in that period, we need to track if the approval is
insufficient then highlight the same where approvals are less than Transactions
OK if no more approval is required ( Approval = transaction )
Excess available where approvals are greater than Transactions
c) At the end of a period of year which is 1st September to 31st August, in case there is any excess approval, the same will get lapsed
d) Based on trend of Transactions and Approvals we should be able to take Approvals in advance
Hope I have not made it too complex......in fact it is a complex problem that needs to be simplified !!