3 Replies Latest reply: Nov 25, 2015 8:10 AM by Marcus Sommer RSS

    Opening Balance and Date Change Issues

      Hi,

       

      I am trying to change an expression for an opening balance figure.

       

      It currently works by summing up all previous periods I have which I have tied to a field called PeriodIndex, which is just numbers from 1-180.

       

      I am now trying to change that to work by a calendar format, i.e Periods 1-12 and Years. I have managed to tie these to PeriodIndex so they do change accordingly, i.e 2016, Period 6 will relate to the correct PeriodIndex number out 1-180.

       

       

      However I am unable to get my opening balance figures correctly.

       

      My original code to sum up all previous periods and to use the minimum selected Periodindex number in a selection was:

       

      Sum({<PeriodIndex={"<$(=Min(PeriodIndex))"},..........Amount)

       

      In an ideal world I could surely transform this code already to select the minimum Fiscal Year and Period to make it something like this i.e:

       

      Sum({<FY={"<$(=Min(FY))"},{<Period={"<$(=Min(Period))"},..........Amount)


      However this expression or versions I've been trying always starts the years first month at 0.



      To perhaps somewhat complicate the matter the opening balance figure is then used to create a closing balance figure for the Period and then in turn this closing balance figure becomes the opening of the next period.


      Any help on the matter would be greatly appreciated.


      James