topic Re: Monthly Avg balance in QlikView

Monthly Avg balance

Anonymous — Mon, 19 Nov 2012 15:22:14 GMT

I have daily balances in my table as following

Key	Date	Balance
1	09/01/2012	10
1	09/02/2012	10
1	09/03/2012	10
1	09/04/2012	20
1	09/05/2012	20
1	09/06/2012	20
1	09/07/2012	20
1	09/08/2012	20
1	09/09/2012	15
1	09/10/2012	15

now the thing is i wana calculate avg weekly monthly and quarterly balance the formula i am using is sum(AGGr(avg(Balance),Week) and when i use the same for the month it shows the same value as in sum(AGGR(AVG(Balance),CalenderMonth) so please guys let me know where i am going wrong.

Re: Monthly Avg balance

swuehl — Mon, 19 Nov 2012 16:40:43 GMT

Week and CalendarMonth are fields in your data model derived from Date?

Where / in which context are you using these expressions?

A small sample file QV file might help to understand your issue.

Re: Monthly Avg balance

RedSky001 — Mon, 19 Nov 2012 17:07:31 GMT

Something like this:

load *

,WeekName(Date) as MyWeekName

,QuarterName(Date) as MyQuarterName

,MonthName(Date) as MyMonthName

;

LOAD Date(Date) as Date,

Balance

FROM

(biff, embedded labels, table is Sheet1$);

Then you cna use the following:

=Avg(Aggr(sum(Balance),MyWeekName))

=Avg(Aggr(sum(Balance),MyQuarterName))

=Avg(Aggr(sum(Balance),MyMonthName))

Mark

Re: Monthly Avg balance

Anonymous — Tue, 20 Nov 2012 08:26:18 GMT

This only works if i don't select a date but when i select a date it shows the same value for all the keys i.e if there is 1,2,3 it will show the same value for 1,2,3.

1,2,3 can be values in the column name KEY.

Re: Monthly Avg balance

RedSky001 — Tue, 20 Nov 2012 10:47:08 GMT

As per swuehl's comments can you provide more informaiton. If you could answer his quesitons and also include the actual document that would be usefull.

Mark

Re: Monthly Avg balance

Anonymous — Tue, 20 Nov 2012 11:24:27 GMT

Finally its done guys hope it helps others