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

    Calculating rolling 7 days averages in script

    Joseph Tournas

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

          • Re: Calculating rolling 7 days averages in script
            Friedrich Hofmann

            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
              sujeet singh

              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
                  Joseph Tournas

                  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
                      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 ;


                        • Re: Calculating rolling 7 days averages in script
                          Joseph Tournas

                          Just provided sample data\

                           

                          DATEEmployeeAmount 1
                          10/1/2015Adam5
                          10/1/2015George5
                          10/1/2015George10
                          10/1/2015George5
                          10/1/2015George5
                          09/30/2015Adam5
                          09/30/2015Adam5
                          09/30/2015Adam10
                          09/30/2015George5
                          09/28/2015George10
                          09/28/2015George5
                          09/28/2015George10
                          09/23/2015Adam5
                          09/23/2015George10
                          09/23/2015George10
                          09/23/2015George10

                           

                           

                          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
                      Bunny V

                      Can you provide sample app or data.

                       

                      Thanks ,

                      Bunny

                        • Re: Calculating rolling 7 days averages in script
                          Joseph Tournas

                          Something like this

                           

                              

                          DATEEmployeeAmount 1
                          10/1/2015Adam5
                          10/1/2015George5
                          10/1/2015George10
                          10/1/2015George5
                          10/1/2015George5
                          09/30/2015Adam5
                          09/30/2015Adam5
                          09/30/2015Adam10
                          09/30/2015George5
                          09/28/2015George10
                          09/28/2015George5
                          09/28/2015George10
                          09/23/2015Adam5
                          09/23/2015George10
                          09/23/2015George10
                          09/23/2015George10

                           

                           

                          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
                              Vincent Ardiet

                              Ah the average is also by employee, so:

                               

                              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

                              , employee_id

                              , date(transac_date+6) as transac_window_date

                              , amount

                              resident transaction_table ;

                               

                              inner join (tmp1_dailytransactions)

                              interval match (transac_date, employee_id)

                              load distinct

                                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)

                              load

                                transac_date

                              , transac_window_date

                              , employee_id

                              , 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 ;

                                • Re: Calculating rolling 7 days averages in script
                                  Joseph Tournas

                                  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;

                                  LOAD * INLINE [

                                  BUSDATE,    Employee,    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

                                   

                                  ];

                                   

                                   

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

                                  • Re: Calculating rolling 7 days averages in script
                                    Joseph Tournas

                                    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
                                        Vincent Ardiet

                                        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
                                            Joseph Tournas

                                            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

                                            1/10/2015 Adam     5            6.66667

                                            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
                                                Vincent Ardiet

                                                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
                                                    Joseph Tournas

                                                    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/2015Adam56.6666667
                                                    1/10/2015George25
                                                    30/09/2015Adam20
                                                    30/09/2015George56.6666667
                                                    28/09/2015George25
                                                    23/09/2015Adam5
                                                    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
                                                        Joseph Tournas

                                                        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
                                                          • Re: Calculating rolling 7 days averages in script
                                                            Joseph Tournas

                                                            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
                                                                Vincent Ardiet

                                                                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

                                            • Re: Calculating rolling 7 days averages in script
                                              Bunny V

                                              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
                                                santhosh n

                                                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