Avg Balances over 60 Days

[groveliam]

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

[hamza99a]

I need help with this as well. If anyone can help mahlerma‌shankarece‌youssefbelloum‌jimhalpert‌

[YoussefBelloum]

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]

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]

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]

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]

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]

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]

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]

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