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

[#1]

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

[#2]

Hi,

I am not sure if I understood your requirements exactly, but I tried a solution, pls. see the attached file.

Best regards

http://quickdevtips.blogspot.com/

[#3]

sum(if(aggr(sum(), Year,Month,[Group Relationship])>0,

aggr(sum(), Year,Month,[Group Relationship],[Group Client Relationship]),0))

I was able to use your examples to verify that the expression I have is constructed correctly. I was trying to apply some of your examples to then pick the prior period (same month prior year) but it doesn’t return the correct amounts it returns current year amounts. See below:

sum(if(aggr(sum({1<[Date]={">=$(=addmonths(monthend(max())+1,-12)) <=$(=max())"}>} ), Year,Month,[Group Relationship])>0,

aggr(sum({1<[Date]={">=$(=addmonths(monthend(max())+1,-12)) <=$(=max())"}>} ), Year,Month,[Group Relationship],[Group Client Relationship]),0))

[#4]

I am sorry I sent the wrong example;

The other was for 13 month rolling which I have the same issue with.

sum(if(aggr(sum({1<Year={'$(vPriorYear)'},Month={'=$(=Max(Month))'}>} ), Year,Month,[Group Relationship])>0,

aggr(sum({1<Year={'$(vPriorYear)'},Month={'=$(=Max(Month))'}>} ), Year,Month,[Group Relationship],[Group Client Relationship]),0))