topic Question Calculate Prior Period Amounts using Aggr function URGENT in QlikView

Question Calculate Prior Period Amounts using Aggr function URGENT

Fri, 08 Jun 2012 13:00:00 GMT

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

Question Calculate Prior Period Amounts using Aggr function URGENT

chematos — Fri, 08 Jun 2012 15:33:26 GMT

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

Question Calculate Prior Period Amounts using Aggr function URGENT

Fri, 08 Jun 2012 17:25:40 GMT

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.

Re: Question Calculate Prior Period Amounts using Aggr function URGENT

Tue, 12 Jun 2012 23:11:06 GMT

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

Re: Question Calculate Prior Period Amounts using Aggr function URGENT

Wed, 13 Jun 2012 00:04:46 GMT

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

Re: Question Calculate Prior Period Amounts using Aggr function URGENT

Mon, 15 Jul 2013 14:58:36 GMT

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))