topic Re: Avg Balances over 60 Days in QlikView

Avg Balances over 60 Days

groveliam — Wed, 25 Jul 2018 14:05:41 GMT

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

Re: Avg Balances over 60 Days

hamza99a — Wed, 25 Jul 2018 16:00:00 GMT

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

Re: Avg Balances over 60 Days

YoussefBelloum — Wed, 25 Jul 2018 16:07:42 GMT

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

Re: Avg Balances over 60 Days

hamza99a — Thu, 26 Jul 2018 11:51:33 GMT

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‌

Re: Avg Balances over 60 Days

YoussefBelloum — Thu, 26 Jul 2018 11:55:04 GMT

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

Re: Avg Balances over 60 Days

hamza99a — Thu, 26 Jul 2018 12:10:28 GMT

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‌

Re: Avg Balances over 60 Days

YoussefBelloum — Thu, 26 Jul 2018 12:19:59 GMT

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

would you be able to share your script ?

Re: Avg Balances over 60 Days

hamza99a — Thu, 26 Jul 2018 12:36:45 GMT

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.

Re: Avg Balances over 60 Days

YoussefBelloum — Thu, 26 Jul 2018 12:39:57 GMT

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;

Re: Avg Balances over 60 Days

hamza99a — Thu, 26 Jul 2018 13:33:28 GMT

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

Re: Avg Balances over 60 Days

hamza99a — Thu, 26 Jul 2018 13:45:50 GMT

bc i now have the averages, I just need to do an average of the average based on the production date being 60 days back.

Re: Avg Balances over 60 Days

YoussefBelloum — Thu, 26 Jul 2018 14:05:23 GMT

if most of the time, there is always Script and front end solutions..

the above script didn't work for you ?

even if you use avg() on the script, you will have to sum those values using sum() in the front end (on the expression of your table)

Re: Avg Balances over 60 Days

hamza99a — Thu, 26 Jul 2018 14:26:50 GMT

my curr_bal amt is showing the same thing as the avg_bal_amt for some reason.

Re: Avg Balances over 60 Days

YoussefBelloum — Thu, 26 Jul 2018 14:33:02 GMT

maybe the data is coming already aggregated, in this case, i think avg will have the same result as the sum..

maybe stalwar1‌ can help here ?

as mentioned above, OP try to take the avg for each account number every day (like he mentioned above)

OP SAMPLE DATA:

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

I suggested a Group by, but didn't work for him..

do you have any idea here ?

Re: Avg Balances over 60 Days

hamza99a — Thu, 26 Jul 2018 15:04:00 GMT

I believe it is using set analysis, but I am fairly new to qlik sense so I needed help with that lol

Re: Avg Balances over 60 Days

YoussefBelloum — Thu, 26 Jul 2018 15:12:23 GMT

we will try to wait for sunny's thoughts, otherwise we will proceed step by step until the expected output

Re: Avg Balances over 60 Days

hamza99a — Thu, 26 Jul 2018 15:18:59 GMT

Thank you for your help!

Re: Avg Balances over 60 Days

Anonymous — Thu, 26 Jul 2018 17:22:10 GMT

Hi Liam,

PFA.

Re: Avg Balances over 60 Days

hamza99a — Thu, 26 Jul 2018 17:23:47 GMT

Hello John,

we are using qlik sense for this project, not qlikview

Re: Avg Balances over 60 Days

Anonymous — Thu, 26 Jul 2018 17:50:01 GMT

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