Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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 NoPlan DateReceiptAmount
1001-3-20175000
1011-3-20177500
1021-3-201715000

Table2 sample data

Document NoBillingDocumentNoDocument Date
10010001-1-2017
100100131-12-2016
100100225-12-2016
10120002-1-2017
101200130-12-2016
101200222-12-2016
10230013-1-2017
102300229-12-2017
102300321-12-2017

Join/Link between two tables is Document No.

Expected Output:-

Particulars< 180 days> 180 days
1 Debtors (A)
2 PlannedXXXXXXXX
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