Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
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

30 Replies
Highlighted
Creator II
Creator II

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.

Highlighted
Champion
Champion

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)

Highlighted
Creator II
Creator II

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

Highlighted
Champion
Champion

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 ?

Highlighted
Creator II
Creator II

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

Highlighted
Champion
Champion

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

Highlighted
Creator II
Creator II

Thank you for your help!

Highlighted
Contributor III
Contributor III

Hi Liam,

PFA.

Highlighted
Creator II
Creator II

Hello John,

we are using qlik sense for this project, not qlikview

Highlighted
Contributor III
Contributor III

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