Calculating rolling 7 days averages in script

[#1]

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

[vincent_ardiet]

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

[datanibbler]

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

[sujeetsingh]

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

[#5]

Hi Vincent,

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

[#6]

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

[Anonymous]

Can you provide sample app or data.

Thanks ,

Bunny

[Anonymous]

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

[vincent_ardiet]

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

tmp1_dailytransactions:

load

  transac_date

, employee_id

, sum(amount) as total_amount

resident transaction_table

group by

  transac_date

, employee_id ;

tmp2_avgtransactions:

load

  transac_date

, date(transac_date+6) as transac_window_date

, amount

resident transaction_table ;

inner join (tmp1_dailytransactions)

interval match (transac_date)

load

  transac_date

, transac_window_date

, amount

resident tmp2_avgtransactions ;

dailytransactions:

load

  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 ;

[#10]

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