Question Calculate Prior Period Amounts using Aggr function URGENT

[#1]

I have an input item that is an average that I am grouping by two relationsips: the relationship of the client to the group and the group to another group (group relationships change over a period of time).I am only using the clients whose grouped average funds are positive.

I then total these averages for the year then divide by the number of months selected to get the"average of the average". In order to make it calculate correctly I used the following aggregate function.This works but is a little "clunky". I am now trying to calcaulte three items based on the selected month and or year:

      Same period last year

      YTD Current year

      YTD Prior Year

I have tried all of the suggestions posted in the community and can not make it work. I have tried set analysis, if statement,etc. Please advise:

Sum(if(Aggr(Sum([Average Funds]),[Group Client Relationship],[Group Relationship])>0/GetSelectedCount([Report Period]),

Aggr(Sum([Average Funds]),[Group Client Relationship],[Group Relationship])/GetSelectedCount([Report Period]),0))

Example of data:

Group Client Relationship      Group Relationship  Group Period   Region  Average Funds

0001                                   2990                       201201            01         100.00

0001                                   2990                       201202            01         -50.00

0001                                   2990                       201203            01         400.00

0001                                   2990                       201101            01         200.00

0001                                   2990                       201102            01         600.00

0001                                   2990                       201103            01         100.00

0002                                   2990                       201201            01         100.00

0002                                   2990                       201202            01         150.00

0002                                   2990                       201203            01         400.00

0002                                   2990                       201101            01         -200.00

0002                                   2990                       201102            01         600.00

0002                                   2990                       201103            01         100.00

[chematos]

I`m sorry but the problem is not clear for me. I must do something similar but I resolved almost all in the load script.

Take a look to my case if you want but first of all, it would be a good thing to have a Calendar and include the periods in that calendar.

http://community.qlik.com/message/225341#225341

hope this helps

[#3]

I have a calendar, I generate it from the period date. My records are monthly so I have a single record for each Group Period for each (group client and group relationship) which is represented below.. So I took the period 201103 and concatenanted an 01 to it to give me a "Date". I also created variables for the different reporting periods - prior year, prior month, etc. and created in the load script a Year, Month, Day from date.

I can't do it in the load script because based on the period selected the total per (group client relationship, group relationship) - could change from positive to negative which would then determine if the client is included or excluded in the reporting period.

I realligned the headings so that it is easier to read. The day01 is generated from the period so they are all

Group Client Relationship      Group Relationship  Group Period   Region  Average Funds

0001                                                     2990                       201201            01         100.00

0001                                                     2990                       201202            01         -50.00

0001                                                     2990                       201203            01         400.00

0001                                                     2990                       201101            01         200.00

0001                                                     2990                       201102            01         600.00

0001                                                     2990                       201103            01         100.00

0002                                                     2990                       201201            01         100.00

0002                                                     2990                       201202            01         150.00

0002                                                     2990                       201203            01         400.00

0002                                                     2990                       201101            01         -200.00

0002                                                     2990                       201102            01         600.00

0002                                                     2990                       201103            01         100.00

The psuedo code from my expression is:

if the total of the average funds for each (group client relationship and group relationsip) is positive for the period selected,

total the average funds for each (group client relationship and group relationsip) then divide by the number of period selected to get the average of the average funds

Display the results for the current period selected,the same period last year,the ytd for the current period slected and the YTD prior year.

The period selected is derrived from the load of the records using group period concatenadted with 01.

[#4]

Hi,

I am probably missing the point.

Could you explain what is wrong with simply using AVG function as in the attached file?

Regards,

http://quickdevtips.blogspot.com

[#5]

The values that I am working with are averages unfortunately do I have to average them but no of mints selected. I figured out how to do that.

Thank you for responding.

Debbie Cook

[#6]

I solved this issue myself but it took some time to come up with the correct code. This works perfectly and I thought I would share it with others that may be presented with the same issue.

Problem:

I had a group of customers whose membership in the group changed dynamically. The determination of the risk of any customer within the group was based on the total of the average funds of the entire "current group". The negative amounts in the average funds column caused the total of the average funds of the "current group" risk level to be lower than it actually was. I did not want to ignore the credits on the load because I used filters to change the membership in the group for forecasting purposes.

I also wanted to be able to have a 13 month rolling analysis of average funds employed.

ex.1 current group

sum(if(aggr(sum([Group Average Funds Employed w/o Collection Days]),[Type],[Region],[Group Relationship],[Name])>0,

aggr(sum([Group Average Funds Employed w/o Collection Days]),[Type],[Region],[Group Relationship],[Name]),0))

ex.2 13 month rolling based on historic group relationship (year/month is effective date of group membership)

=Sum({1}if(Aggr(Sum({1<[Date]={">=$(=addmonths(monthend(max([Date]))+1,-13)) <=$(=max([Date]))"}>}

[Group Average Funds Employed w/o Collection Days]),[Year], [Month],[Group Relationship])>0,

Aggr(Sum({1<[Date]={">=$(=addmonths(monthend(max([Date]))+1,-13)) <=$(=max([Date]))"}>}

[Group Average Funds Employed w/o Collection Days]),[Year], [Month],[Group Relationship]),0))