Hello Guys,
I'm facing issue while calculating bucket in Pivot Chart on the basis of Difference in days between Date1 and Date2.
And Table1 contains 1 record(1 date) against a 1 ID i.e Master table.
Table1 sample data
| Document No | Plan Date | ReceiptAmount |
|---|
| 100 | 1-3-2017 | 5000 |
| 101 | 1-3-2017 | 7500 |
| 102 | 1-3-2017 | 15000 |
Table2 sample data
| Document No | BillingDocumentNo | Document Date |
|---|
| 100 | 1000 | 1-1-2017 |
| 100 | 1001 | 31-12-2016 |
| 100 | 1002 | 25-12-2016 |
| 101 | 2000 | 2-1-2017 |
| 101 | 2001 | 30-12-2016 |
| 101 | 2002 | 22-12-2016 |
| 102 | 3001 | 3-1-2017 |
| 102 | 3002 | 29-12-2017 |
| 102 | 3003 | 21-12-2017 |
Join/Link between two tables is Document No.
Expected Output:-
| Particulars | < 180 days | > 180 days |
|---|
| 1 Debtors (A) | | |
| 2 Planned | XXXX | XXXX |
| 3 Un-planned | | |
| 4 Supply Proforma | | |
| 5 Planned | | |
| 6 Un-planned | | |
| 7 Sub-Total | | |
Now i want to calculate bucket for Particulars = 2 Planned and nos needs to plotted on the days diff between [Plan Date] and [Document Date]. i.e existing expression used as mentioned below:-
=if([Plan Date]-[Document Date]<=180 and Particulars='2 Planned' and Bucket='< 180 days',sum([Receipt Amount]))
I cannot join Table1 and Table2 because frequency of Document No in Table1 is 1 and frequency of Document No is more than 1 i.e one to many relationship and frequency of Receipt Amount gets multiplied.
Additionally, Particulars and Bucket are stored in Inline tables.
Kindly help me.
Thanks in Advance,
Girish
