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]

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.

[YoussefBelloum]

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)

[hamza99a]

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

[YoussefBelloum]

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 ?

[hamza99a]

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

[YoussefBelloum]

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

[hamza99a]

Thank you for your help!

[Anonymous]

Hi Liam,

PFA.

[hamza99a]

Hello John,

we are using qlik sense for this project, not qlikview

[Anonymous · Accepted solution]

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