Hi,
So for context, I have four tables namely:
1. Item Table: Contains unique list of items with their parent (stock group)
2. Stock Group Table: Hierarichal table with stock group & its subsequent parent. It also contains a key which says if the stock group is primary (no parent)
3. Scheme Table: Contains schemes that the business run on its items. Schemes are defined on stock groups (not necessarily at the lowest level of hierarchy). This table has no relationship with any other table in the data model.
4. Transaction Table: Contains datewise sales transactions of different items with quantity and amounts.
| Item Table |
| Stock Item | Stock Group |
| A | AP |
| B | BP |
| C | CP |
| Stock Group Table |
| Stock Group | Stock Group Parent | Is Primary? |
| AP | APP | N |
| BP | BPP | N |
| CP | CPP | N |
| APP | APPP | N |
| BPP | BPPP | N |
| CPP | | Y |
| APPP | | Y |
| BPPP | | Y |
| Schemes Table |
| Scheme | Stock Group | From Date | To Date |
| SCH1 | CPP | 01-04-2023 | 30-04-2023 |
| SCH1 | APP | 01-04-2023 | 30-04-2023 |
| SCH2 | BP | 01-04-2023 | 30-06-2023 |
| SCH3 | BPPP | 01-04-2023 | 31-08-2023 |
| SCH3 | APPP | 01-04-2023 | 31-08-2023 |
| Transaction Table |
| Date | Voucher No. | Item | Qty | Amount |
| 02-04-2023 | V-001 | A | 5 | 200 |
| 02-04-2023 | V-001 | B | 5 | 300 |
| 04-05-2023 | V-002 | B | 10 | 500 |
| 04-05-2023 | V-002 | C | 10 | 500 |
Now I want a measure which will give teh total sales amount for each scheme. Since the level of the stock group in the hierarchy can differ, I am not sure how to code it.
I am thinking along the lines of using pathcontains() and path functions but not getting something accurate..
Really appreciate the help on this one. Thank you:)
