Bucket calculation in Pivot Chart

Anonymous — Wed, 17 May 2017 07:47:39 GMT

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

topic Bucket calculation in Pivot Chart in QlikView

Bucket calculation in Pivot Chart