I have the following in a Qlik Sense pivot table:
Rows:
InvoiceMonth & '-'& Invoice Total
InvoiceMonth > '2016-02'
ascending order: (... 2016-12/ $80M , 2017-01 / $55M, 2017-02 /65$M)
Column:
PaymentSeq (Column: 0,1,2,3,4,5,...,n)
Measures:
[Paid]:
SUM( TOTAL <InvoiceMonth,PayerName,PaymentSeq>Paid) //offset 1, 6-mos back
[Avg Pymts Prev 6-Mts Avg]:
rangeavg(above(SUM( TOTAL <InvoiceMonth,PayerName,PaymentSeq>Paid),1,6)) //offset 1, 6-mos back
[% Paid Last 6 months]:
rangesum(above(SUM( TOTAL <InvoiceMonth,PayerName,PaymentSeq>Paid),1,6)) / rangesum(above(SUM( TOTAL <InvoiceMonth,PayerName>Invoiced),1,6))
The top few rows will give wrong 6-mt averages. I have data for 2015 and 2016, but "ABOVE with TOTAL" won't see beyond what is visible in the 12 -mt table. I need it to calculate the average 6-mt on top rows.
I don't know a way to fix this using the "ABOVE" function. I've tried to adapt to different ways using AGGR but could not get it to work.
Any solution would be very appreciated.
