Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
what is the format of your Production Date field ?
Hey, I didn't realize that I was tagged here... I will take a look to see if I can be of any help...
No problem, thank you
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 .
yes
the above answer don't solve your problem so try to remove the correct answer until your issue is solved
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])
yes
I have no idea what this yes means ....