Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
meckeard
Contributor III
Contributor III

Help getting average balance by AccountNumber/month+year

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!

 

1 Solution

Accepted Solutions
meckeard
Contributor III
Contributor III
Author

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

View solution in original post

4 Replies
meckeard
Contributor III
Contributor III
Author

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

View solution in original post

dilipranjith
Partner
Partner

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

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.

dilipranjith
Partner
Partner

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