Discussion Board for collaboration related to QlikView App Development.
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!
PS this is for Qlik Sense
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)
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:
I suggested a Group by, but didn't work for him..
do you have any idea here ?
for i=0 to 1
date(Today()+2)-[Production Date] AS Noofdays;
[Balance Previous Year],
Date([Close DT] ) AS [Close DT],
FROM [lib://Downloads/Sample Data2.xlsx]
(ooxml, embedded labels, table is Deposits$(Vtable));
Left Join (Deposits)
LOAD [ACCT NBR],
AVG([Current Balance]) AS [Average Balance] Resident Deposits Where Noofdays<=60 group by [ACCT NBR];