Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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/2015 | Adam | 5 | 12.5 |
1/10/2015 | George | 25 | 18.3 |
30/09/2015 | Adam | 20 | 20 |
30/09/2015 | George | 5 | 15 |
28/09/2015 | George | 25 | 27.5 |
23/09/2015 | Adam | 5 | 5 |
23/09/2015 | George | 30 | 30 |
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
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