Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Contributor III
Contributor III

Re: Avg Balances over 60 Days

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
Highlighted
Creator II
Creator II

Re: Avg Balances over 60 Days

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

Highlighted
Champion
Champion

Re: Avg Balances over 60 Days

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

Highlighted
Creator II
Creator II

Re: Avg Balances over 60 Days

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

Highlighted
Champion
Champion

Re: Avg Balances over 60 Days

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

    

Highlighted
Creator II
Creator II

Re: Avg Balances over 60 Days

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

Highlighted
Champion
Champion

Re: Avg Balances over 60 Days

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

would you be able to share your script ?

Highlighted
Creator II
Creator II

Re: Avg Balances over 60 Days

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.

Highlighted
Champion
Champion

Re: Avg Balances over 60 Days

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;

Highlighted
Creator II
Creator II

Re: Avg Balances over 60 Days

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