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]

hello john,

Unfortunately that script is not giving me any good numbers.

I was however able to use a formula like this:

avg({$<[Production Date] = {">=$(=Date(AddDays(max(Production Date), -60), 'YYYY-MM-DD')) <= $(=Date(Max([Production Date]), 'YYYY-MM-DD'))"}>} [Deposits_curr_bal_amt])

The only problem is that when it averages these out it is using my whole data set, whereas I only want the max production date and 59 days prior.

Thank you and Id appreciate your help ! youssefbelloum‌ johndawson‌

[YoussefBelloum]

what is the format of your Production Date field ?

[sunny_talwar]

Hey, I didn't realize that I was tagged here... I will take a look to see if I can be of any help...

[YoussefBelloum]

No problem, thank you

[hamza99a]

the format is YYYY-MM-DD.

I just dragged in the current balance amount and changed the sum to avg. I just need an automatic filter for the date to show the past 60 days .

[sunny_talwar]

I am not sure if OP and hamza99a‌ work together or they happen to have the same exact issue on the same exact day . But what I really would like is if hamza99a‌‌ can provide the exact output they are hoping to get and where (script or front end) based on the input file provided or a new input file.

[hamza99a]

yes

[YoussefBelloum]

the above answer don't solve your problem so try to remove the correct answer until your issue is solved

[YoussefBelloum]

Try to evaluate the date inside your set analysis

avg({$<[Production Date] = {">=$(=Date(AddDays(max(Date#([Production Date],'YYYY-MM-DD')), -60), 'YYYY-MM-DD')) <= $(=Date(Max(Date#([Production Date],'YYYY-MM-DD')), 'YYYY-MM-DD'))"}>} [Deposits_curr_bal_amt])

[sunny_talwar]

yes

I have no idea what this yes means ....