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.
