Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating rolling 7 days averages in script

Hi team,

I am trying to do something tricky and could use some help. I have a number of dates for which I also have a number of transactions. Assume that, as a first step, we do a sum of these transactions per day using a group by but still the group by wont only be on date so we might have a various number of rows per each date.

What I want to do is calculate for each row the average of these sums for that date and the 6 previous ones and save it as a new field. It is worth noting that I dont have all calendar dates available and as I said could be more than one rows per date even after group by so i am not sure how helpful functions like previous () etc can be unless the date can be checked specifically..

Any ideas? This has to be done in the script..

Thanks

22 Replies
Not applicable
Author

So to make my requirement more clear the avg amount in the first row should be 12.5 since we check Adam's amount till 26/09 supposedly so (5+20)/2

Thats what I am looking for:

BUSDATE Employee TAmount avg_amount
1/10/2015Adam512.5
1/10/2015George2518.3
30/09/2015Adam2020
30/09/2015George515
28/09/2015George2527.5
23/09/2015Adam55
23/09/2015George3030
Not applicable
Author

Would be much obliged if you could take a look at the qvw and maybe see if what I need is possible. Please do let me know if the requirement is unclear

vincent_ardiet
Specialist
Specialist

Hi Joseph,

Ok, so, there were 2 mistakes, first my intervalmatch should not use existing field names, so I have renamed in tmp2 the two dates. Then, it's the average of the daily transations and not the average of each transaction, so tmp2 is not more based on transaction_table but on tmp1.

I have also renamed the amount fields to have something easier to understand.

And yes the first code was written without Qlikview, else I will not have wrote "interval match" instead of "intervalmatch" .

Here is the code:


transaction_table:
LOAD * INLINE [
transac_date,employee_id,amount
1/10/2015,Adam,5
1/10/2015,George,5
1/10/2015,George,10
1/10/2015,George,5
1/10/2015,George,5
30/09/2015,Adam,5
30/09/2015,Adam,5
30/09/2015,Adam,10
30/09/2015,George,5
28/09/2015,George,10
28/09/2015,George,5
28/09/2015,George,10
23/09/2015,Adam,5
23/09/2015,George,10
23/09/2015,George,10
23/09/2015,George,10
];


tmp1_dailytransactions:
load
  transac_date
, employee_id
, sum(amount) as daily_amount
resident transaction_table
group by
  transac_date
, employee_id ;

tmp2_avgtransactions:
load
  transac_date as begin_window_date
, employee_id
, date(transac_date+6) as end_window_date
, daily_amount as daily_amount_window
resident tmp1_dailytransactions ;

left join (tmp1_dailytransactions)
intervalmatch (transac_date, employee_id)
load distinct
  begin_window_date
, end_window_date
, employee_id
resident tmp2_avgtransactions ;

left join (tmp1_dailytransactions)
load
  begin_window_date
, end_window_date
, employee_id
, daily_amount_window
resident tmp2_avgtransactions ;


dailytransactions:
load
  transac_date
, employee_id 
, daily_amount
, avg(daily_amount_window) as avg_daily_amount
resident tmp1_dailytransactions
group by
  transac_date
, employee_id 
, daily_amount ;

drop tables tmp1_dailytransactions, tmp2_avgtransactions ;


Regards,

Vincent