22 Replies Latest reply: Oct 13, 2015 10:40 PM by Vincent Ardiet

# 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

• ###### Re: Calculating rolling 7 days averages in script

Hi Joseph,

If you have a SQL database in source, like Oracle or SQLServer you can use a partition by windowing function.

Else, if you can't, maybe you can create a second transaction date in your table which is 7 days after the real one. Then, with a interval match joining the table with itself you will be able to group all the transactions.

I hope it's clear :-).

Regards,

Vincent

• ###### Re: Calculating rolling 7 days averages in script

Hi Vincent,

That sounds promising.. Could you provide a short example if not too much to ask

• ###### Re: Calculating rolling 7 days averages in script

Hi Joseph,

I understand you already have some chart displaying the sum of whatever per day?

There are functions in QlikView - BEFORE and AFTER I think, haven't yet found a use for them, but they ARE useful - which allow you to access the different "points" along your dimension which is the day.

I think that would be a good starting_point for a rolling average in a chart.

Sorry I cannot help you further, as I said I haven't yet done it.

Best regards,

DataNibbler

• ###### Re: Calculating rolling 7 days averages in script

Use previous function in Qlikview or i will sugest is to have all dates sorted and use rowno() and assign them a rank then just take only Rank<=6 as average

• ###### Re: Calculating rolling 7 days averages in script

Hi,

Sorry just changed the description above, I can still have a various number of rows per date even after the group by, lets say by employee id

• ###### Re: Calculating rolling 7 days averages in script

I can't test what I'm writing so you may have some adjustments to do, so for example:

tmp1_dailytransactions:

transac_date

, employee_id

, sum(amount) as total_amount

resident transaction_table

group by

transac_date

, employee_id ;

tmp2_avgtransactions:

transac_date

, date(transac_date+6) as transac_window_date

, amount

resident transaction_table ;

inner join (tmp1_dailytransactions)

interval match (transac_date)

transac_date

, transac_window_date

, amount

resident tmp2_avgtransactions ;

dailytransactions:

transac_date

, employee_id

, total_amount

, avg(amount) as avg_amount

resident tmp1_dailytransactions

group by

transac_date

, employee_id

, total_amount ;

drop tables tmp1_dailytransactions, tmp2_avgtransactions ;

• ###### Re: Calculating rolling 7 days averages in script

Just provided sample data\

 DATE Employee Amount 1 10/1/2015 Adam 5 10/1/2015 George 5 10/1/2015 George 10 10/1/2015 George 5 10/1/2015 George 5 09/30/2015 Adam 5 09/30/2015 Adam 5 09/30/2015 Adam 10 09/30/2015 George 5 09/28/2015 George 10 09/28/2015 George 5 09/28/2015 George 10 09/23/2015 Adam 5 09/23/2015 George 10 09/23/2015 George 10 09/23/2015 George 10

Now imagine the first row like this instead
10/1/2015   Adam   (average of Amount 1 for Adam for dates 10/1, 9/30, 9/29, 9/28, 9/27, 9/26) = 25/4

Thats even though not all dates exist

• ###### Re: Calculating rolling 7 days averages in script

Can you provide sample app or data.

Thanks ,

Bunny

• ###### Re: Calculating rolling 7 days averages in script

Something like this

 DATE Employee Amount 1 10/1/2015 Adam 5 10/1/2015 George 5 10/1/2015 George 10 10/1/2015 George 5 10/1/2015 George 5 09/30/2015 Adam 5 09/30/2015 Adam 5 09/30/2015 Adam 10 09/30/2015 George 5 09/28/2015 George 10 09/28/2015 George 5 09/28/2015 George 10 09/23/2015 Adam 5 09/23/2015 George 10 09/23/2015 George 10 09/23/2015 George 10

Now imagine the first row like this instead
10/1/2015   Adam   (average of Amount 1 for Adam for dates 10/1, 9/30, 9/29, 9/28, 9/27, 9/26) = 25/4

Thats even though not all dates exist

• ###### Re: Calculating rolling 7 days averages in script

Ah the average is also by employee, so:

tmp1_dailytransactions:

transac_date

, employee_id

, sum(amount) as total_amount

resident transaction_table

group by

transac_date

, employee_id ;

tmp2_avgtransactions:

transac_date

, employee_id

, date(transac_date+6) as transac_window_date

, amount

resident transaction_table ;

inner join (tmp1_dailytransactions)

interval match (transac_date, employee_id)

transac_date

, transac_window_date

, employee_id

resident tmp2_avgtransactions ;

// Sorry there was a mistake here in the previous code, amount can't be in the interval match join

inner join (tmp1_dailytransactions)

transac_date

, transac_window_date

, employee_id

, amount

resident tmp2_avgtransactions ;

dailytransactions:

transac_date

, employee_id

, total_amount

, avg(amount) as avg_amount

resident tmp1_dailytransactions

group by

transac_date

, employee_id

, total_amount ;

drop tables tmp1_dailytransactions, tmp2_avgtransactions ;

• ###### Re: Calculating rolling 7 days averages in script

Although I can follow the idea it doesn't seem to work. In my model I start with one table only (so transaction_table is no longer needed) which you can get by copy pasting this. So you can assume we start from your tmp1_dailytransactions that is.

Data:

LOAD date(BUSDATE) as BUSDATE, Employee, sum(Amount) as TAmount

group by date(BUSDATE), Employee;

BUSDATE,    Employee,    Amount

1/10/2015,    George,    5

1/10/2015,    George,    10

1/10/2015,    George,    5

1/10/2015,    George,    5

30/09/2015,    George,    5

28/09/2015,    George,    10

28/09/2015,    George,    5

28/09/2015,    George,    10

23/09/2015,    George,    10

23/09/2015,    George,    10

23/09/2015,    George,    10

];

Then I am trying to create the inner join and the interval match unsuccessfully..

• ###### Re: Calculating rolling 7 days averages in script

Worth mentioning that we need to go 6 days BACK so date(transac_date - 6) as transac_window_date

• ###### Re: Calculating rolling 7 days averages in script

No transaction_table is useful because it is used twice. Create it with your inline table.

Then, if you want to go 6 days back, it's +6 and not -6. For example with the lines "23/09/2015,    George,    10", with the +6, transac_windows_date will be the 29th. So then, when you will join with the total amount of the 29th, the line from the 23rd will be picked up. I know it's not natural but when you want to associated past data with future data it's easier to move forward the date of the past.

• ###### Re: Calculating rolling 7 days averages in script

Ok so it runs now but not giving the desired results, by mirroring the above I get

transac_date, employee_id  , total_amount, avg_amount

30/09/2015 George        5            6.66667

so dates & employees combos missing and the total and average amount is not right either. What i am after is one row per date&employee giving the total amount (thats the tmp1_dailytransactions table basically) and then the avg amount which for 1/10/2015 and Adam is all Adam's amount for all days till 25/09, so that's it
(5 + 5 + 5 + 10) / 4 = 6.25

• ###### Re: Calculating rolling 7 days averages in script

Sorry I have no QlikView to test (no version for Android yet ).

I think that the Inner Join are a mistake,  replace all of them by left join.

• ###### Re: Calculating rolling 7 days averages in script

Are you following this code by eye? Yes I had changed that myself which fixed the missing rows thing but averages still wrong:

BUSDATE Employee TAmount avg_amount
1/10/2015George25
30/09/2015George56.6666667
28/09/2015George25
23/09/2015George30

I have attached the whole script for you to check when you get the chance

• ###### Re: Calculating rolling 7 days averages in script

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/2015George2518.3
30/09/2015George515
28/09/2015George2527.5
23/09/2015George3030
• ###### Re: Calculating rolling 7 days averages in script

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

• ###### Re: Calculating rolling 7 days averages in script

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:
transac_date,employee_id,amount
1/10/2015,George,5
1/10/2015,George,10
1/10/2015,George,5
1/10/2015,George,5
30/09/2015,George,5
28/09/2015,George,10
28/09/2015,George,5
28/09/2015,George,10
23/09/2015,George,10
23/09/2015,George,10
23/09/2015,George,10
];

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

tmp2_avgtransactions:
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)
begin_window_date
, end_window_date
, employee_id
resident tmp2_avgtransactions ;

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

dailytransactions:
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

• ###### Re: Calculating rolling 7 days averages in script

Rolling can be done by ,

Range sum

or Above function which helps in rolling previous days results

or by having aggr with range sum function.

thanks,

bunny

• ###### Re: Calculating rolling 7 days averages in script

Hi Joseph,

Use two variables and assign the selected date in one variable and assign the (selected date -6) in 2nd variable.

and in the expression using set analysis you can calculate the average.

BR,

SK

• ###### Re: Calculating rolling 7 days averages in script

Hi Santhosh,

This needs to be done in the script unfortunately