0 Replies Latest reply: Mar 6, 2017 8:27 AM by Val Barnes RSS

    Date range calculations using Above.

    Val Barnes

      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.