Help getting average balance by AccountNumber/month+year

[meckeard]

Hi all,

I have the following table with end of day account balance:

AccountNumber
Bal_Date (ex 9/1/2019)
Balance

Here is a small sample of data:

00000000001 - $1000.00 - 8/1/2019 - Aug - 2019
00000000001 - $900.00 - 8/2/2019 - Aug 2019

00000000001 - $1000.00 - 8/3/2019 - Aug 2019
00000000001 - $900.00 - 8/4/2019
-- more for August 2019
00000000001 - $1000.00 - 9/1/2019 - Sep - 2019
00000000001 - $1000.00 - 9/2/2019 - Sep - 2019
00000000001 - $1000.00 - 9/3/2019 - Sep - 2019
00000000001 - $1000.00 - 9/4/2019 - Sep - 2019

Now I need to create a new table that will hold the average monthly balance for each AccountNumber/Month+Year. Since I will have numerous months worth of data, there will be 1 record for each AccountNumber/Month+Year, like this:

00000000001 - $1000.00 - Aug 2019
00000000001 - $900.00 - Sep 2019

Since I have millions of records, this seems like a task to be run in the load script but I can't get the above table. I can get the AccountNumber/Month+Year and the AccountNumber+Avg Monthly Balance but not all 3. I've tried many iterations of avg, aggr, etc and still nothing. Here is my latest script. It generates an error but I want to include it to give you an idea of what I am trying to do:

Balance:
LOAD AccountNumber,
Bal_Date,
Balance,
Month(Bal_Date) as BalanceMonthName,

Year(Bal_Date) as BalanceYearName
FROM [Data\Up To 10-04-2019\Balance.qvd] (qvd);

// Calculate avg monthly balance by month/customer.
AverageCustomerBalance:
Load Distinct AccountNumber,
BalanceMonthName,
BalanceYearName,
Avg(Balance) as AverageMonthlyBalance
Resident Balance
group by AccountNumber;

Thanks!

[meckeard · Accepted solution]

I figured this out about 10 minutes after posting (see  below) although I'm open to suggestions for better ways to do it:

AverageCustomerBalance:
LOAD distinct AccountNumber,
Avg(Balance) as AverageBalance,
month(Bal_Date) as MonthName,
year(Bal_Date) as YearName
Resident Balance
GROUP BY AccountNumber,
month(Bal_Date),
year(Bal_Date);

[dplr-rn]

that looks allright. but why do you want to do this in script?
Why not do this in a chart?

[meckeard]

I couldn't get it to work in a chart and with millions of records, I thought it would be more efficient doing it in the load script.

[dplr-rn]

it would be faster but less flexible if you do in script e.g. if you want to filter this average by some other dimension.

But as this is at a i think low granular level  (account number) it should be ok.

It should work front end. What did you try?

p.s. million rows is generally easily handled by qlik