
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Bucket calculation in Pivot Chart
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
