Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
groveliam
Creator
Creator

Avg Balances over 60 Days

Hey everyone,

I need the average balance for each account number over the past 60 days. Each day is held in a separate file (e.g. deposits_20180724). Is there a way to add a column or variable that takes the average balance as the days are loaded in. I am also Storing the files into one large QVD so maybe there is a way to Peek into them and get the average balance where the account numbers are the same. Also maybe there is a way with a For Each loop. I will try to attach sample data with 2 days of info. Any help is appreciated!

Thank you,

Liam Grover

PS this is for Qlik Sense

1 Solution

Accepted Solutions
Anonymous
Not applicable

Query:


for i=0 to 1
LET Vtable=28-$(i);
Deposits:
LOAD *,
date(Today()+2)-[Production Date] AS Noofdays;
LOAD
[ACCT NBR],
[ACCT Type],
[Current Balance],
[Production Date],
[Tax ID],
[Branch Number],
[Balance Previous Year],
[Open DT],
Date([Close DT] ) AS [Close DT],
[Government Flag]
FROM [lib://Downloads/Sample Data2.xlsx]
(ooxml, embedded labels, table is Deposits$(Vtable));
NEXT
Left Join (Deposits)

LOAD [ACCT NBR],
AVG([Current Balance]) AS [Average Balance] Resident Deposits Where Noofdays<=60 group by [ACCT NBR];

View solution in original post

30 Replies
hamza99a
Creator II
Creator II

I need help with this as well. If anyone can help mahlermashankareceyoussefbelloumjimhalpert

YoussefBelloum
Champion
Champion

Hi,

your Date/day file (which represents the date of the balance account) is not linked to any field present in the file structure ?

you can simply concatenate (implicitely or explicitely) all your files and peek a portion from the file name as field while loading it.. no need for a loop

hamza99a
Creator II
Creator II

I have production dates loaded in and concatenated. All I need to do is take the avg for each account number every day. If you could help me with the set analysis portion of this it would be greatly appreciated.

my fields are as follows:

acct_nbr        production_date     currentbalance amount

1001                 07/25/2018              10

1001                 07/25/2018              14

(I need to create this)

new column:  avg_balance:              production date:

                      12                                    07/25/2018

Thank you for the reply! youssefbelloum

YoussefBelloum
Champion
Champion

Hi,

you will need to use avg() and group by your dimensions on the load

something like this:

table:

LOAD

          acct_nbr,

          production_date,

          avg(currentbalance amount) as currentbalance amount

FROM...

Group by 

          acct_nbr,

          production_date;


give it a try and let me know

    

hamza99a
Creator II
Creator II

Hi,

That looks like it should do it ,but the group by clause is not working. I am loading in a qvd and the files that I am pulling from are csv files. I don't know if group by doesn't work in this instance or not.

Thanks youssefbelloum

YoussefBelloum
Champion
Champion

AFIAK there is no restrictions on the type of source loaded to make a Group by..

would you be able to share your script ?

hamza99a
Creator II
Creator II

Hello Youssef,

My script:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;-$#,##0.00';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET CreateSearchIndexOnReload=1;

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

LOAD

    "Production Date",

    acct_nbr,

    acct_nm,

    acct_stat_cd,

    "Branch Number",

    Deposits_curr_bal_amt,

    Avg(Deposits_curr_bal_amt)as avg_bal_amt,

    open_dt,

    mtd_avg_bal_amt,

    Government_Flag,

    AccountType,

    Deposits_close_dt,

    GovAccType

FROM [lib://extracts_rel/Deposit.qvd](qvd)

Group By

acct_nbr,

"Production Date";

Error Code:

Started loading data

Deposit Script error: Invalid expression

The following error occurred:

Invalid expression

The error occurred here:

LOAD "Production Date", acct_nbr, acct_nm, acct_stat_cd, "Branch Number", Deposits_curr_bal_amt, Avg(Deposits_curr_bal_amt)as avg_bal_amt, open_dt, mtd_avg_bal_amt, Government_Flag, AccountType, Deposits_close_dt, GovAccType FROM [lib://extracts_rel/Deposit.qvd](qvd) Group By acct_nbr, "Production Date"

The following error occurred:

Invalid expression

The error occurred here:

?

Data has not been loaded. Please correct the error and try loading again.

YoussefBelloum
Champion
Champion

to aggregate your data on the script using a Group by clause, you need to put in your Group by all the fields that don't include an aggregation function (avg here)

for your example, the right code is this:

LOAD

    "Production Date",

    acct_nbr,

    acct_nm,

    acct_stat_cd,

    "Branch Number",

    Deposits_curr_bal_amt,

    Avg(Deposits_curr_bal_amt)as avg_bal_amt,

    open_dt,

    mtd_avg_bal_amt,

    Government_Flag,

    AccountType,

    Deposits_close_dt,

    GovAccType

FROM [lib://extracts_rel/Deposit.qvd](qvd)

Group By

"Production Date",

    acct_nbr,

    acct_nm,

    acct_stat_cd,

    "Branch Number",

    Deposits_curr_bal_amt,

     open_dt,

    mtd_avg_bal_amt,

    Government_Flag,

    AccountType,

    Deposits_close_dt,

    GovAccType;

hamza99a
Creator II
Creator II

Hello Youseff.

Is there any date function using set analysis that allows me to find the average of the balance amt from the max production date to 60 days back. Keep in mind I have missing data for sundays.

Thanks