2 Replies Latest reply: Apr 15, 2013 9:30 AM by Radovan Oresky RSS

    Last balance value in pivot

    Radovan Oresky



      I have a problem with getting the last value of balance sheet accounts in the pivot table. Does anybody has an expression which could do it?


      My case is like this:

      - closing balances are calculated in the script for every account and every day

      - I need to get a pivot table where dimensions are month, week and accounts group

      - I need to get an expression where for any shown period it sums the balances for all selected accounts but only from the last day of that period


      I attached an excel whit desired result.

        • Re: Last balance value in pivot
          Jason Michaelides

          Do you have a calendar table in your QV data model?  If not, create one. There are lots of examples in this forum of how to do this.


          One way of getting what you want is to ensure you have IsMonthEnd and IsWeekEnd fields in the calendar model. You can create these in the calendar load by:


          If(Date=MonthEnd(Date),1,0)        AS     IsMonthEnd

          ,If(Date=WeekEnd(Date)-2,1,0)     AS     IsWeekEnd


          Then, in your chart with Week as the last dimension your expression can be:


          Sum({<IsWeekEnd={1}>} Balance)


          And in the chart to month level:


          Sum({<IsMonthEnd={1}>} Balance)


          Hope this helps,



            • Re: Last balance value in pivot
              Radovan Oresky

              Thanks, this works nicely in the restricted circumstances. Trouble is when adding accounts group dimension in front of the month. When I collapse, it will show the sum of balances, but the last balance from the last month is needed.


              I used this expression, which works until date level, but without week level:


              If(Sum({<IsMonthEnd={1}>}Balance)=0, Sum(Balance), Sum({<IsMonthEnd={1}>}Balance))


              Then, when the pivot is expanded on monthly level it works fine, e.g.:


              Account GroupMonthBalance


              But when I collapse the group, the sum is incorrect, e.g.:


              Account GroupBalance
              Group1300 - but should be 200


              Also, when I want to have month and week in the same pivot and I expand the expression, then when the date for the end of the week is the same as the date for the end of the month, value will duplicate.


              So, I guess I hoped there is a more general function, which would always show the last value (timewise) for whichever dimension. I will try to optimize it further.


              But really thanks.