3 Replies Latest reply: Jun 13, 2012 7:26 AM by Debbie Cook RSS

    Questions: STUCK - Set Analysis - Current and Prior Period where the total is > 0

      I have tried organizing this in many ways but I have been stuck on this for a week. I am trying to sum the net total of Funds for the group where the total of funds for the master group is > 0 for the current period and the prior period. So far I can't get the first one to give me the right amount it keeps giving me the total of the master group not the group. I just want to use the total of the master group to select the groups I want to include in my total. Second when I do the same calculation for the perious period it includes the last selected month of the current year. I really want the period to be this year up to month selected and last year up to same month selected.

       

      I use a Master Calendar and select year = 2014 and month = 4.

       

       

      =sum({$<[Master Group] = {"Sum={1<Year = {'$(=max(Year))'} >} [Funds]) > 0"},[Group]={"=Sum({1<Year = {'$(=max(Year))'} >} [Group Average Funds Employed w/o Collection Days])"},[Report Period]=>} [Funds] )

       

      =sum({$<[Master Group] = {"Sum={1<Year = {'$(=max(Year)-1)'} >} [Funds]) > 0"},[Group]={"=Sum({1<Year = {'$(=max(Year)-1)'} >} [Group Average Funds Employed w/o Collection Days])"},[Report Period]=>} [Funds] )

       

      Data:

      Master Group  Group Report Period           Fund

      5                        1             01/01/2012       1.00   

      5                        2             01/01/2012      -1.00

      5                        3             01/01/2012       1.00

      5                        4             01/01/2012       1.00

      1                        1             02/01/2012      -1.00

      5                        1             02/01/2012       1.00   

      5                        2             02/01/2012      -1.00

      5                        3             02/01/2012       1.00

      5                        4             02/01/2012       1.00

      1                        1             02/01/2012      -1.00

      5                        1             03/01/2012       1.00   

      5                        2             03/01/2012      -1.00

      5                        3             03/01/2012       1.00

      5                        4             03/01/2012       1.00

      1                        1             03/01/2012      -1.00

      5                        1             01/01/2011       1.00   

      5                        2             01/01/2011      -1.00

      5                        3             01/01/2011    -  1.00

      5                        4             01/01/2011       1.00

      1                        1             02/01/2011      -1.00

      5                        1             02/01/2011       1.00   

      5                        2             02/01/2011      -1.00

      5                        3             02/01/2011       1.00

      5                        4             02/01/2011       1.00

      1                        1             02/01/2011      -1.00

      5                        1             03/01/2011       1.00   

      5                        2             03/01/2011      -1.00

      5                        3             03/01/2011      -1.00

      5                        4             03/01/2011       1.00

      1                        1             03/01/2011      -1.00