topic Re: Avg time between events - avg aggr above problem in QlikView

Avg time between events - avg aggr above problem

Hakisback — Sat, 16 Nov 2024 03:48:24 GMT

Hello,

I have a simple table where I have different users with dates corresponding to 2 types of events

pruchasing or visit. I've also created a field called date_common which concatenates both events for a user.

So I have a table like (see file enclosed)

User Date_common Date_purchase Date_visit flag_purchase flag_visit

When I load the data I make sure to have an "order by User,Date_common" so that I am sure that the data is sorted in chronological order for any user (very important for above function)

Now I am trying to calculate for these 2 types of event (purchase and visit)

the average time between 2 events (so average time between 2 purchases and average time between 2 visits)

If you look at my data we should have

avg time between 2 purchases : 43min

avg time between 2 visits : 53min

I can find these results when creating a straight table.

but when I want to aggregate to have this expression in a single text box I have a totally different result

The expression in my text box is (avg time between 2 purchases in minutes)

=avg(aggr(date_purchase -above(date_purchase ),user,date_purchase ))*24*60)

which gives me something like '-2' and I don't understand where it comes from

(same goes for date_visit with this expression using date_visit I find -16minutes)

I've tried to sort the data using the script in different ways but no matter the sort I still find these same values that are wrong.

Can you please help me ?

Thank you very much

Re: Avg time between events - avg aggr above problem

rubenmarin — Fri, 19 Apr 2019 11:54:02 GMT

Hi, I'm not seeing the same issue, data in text box also says 43 min, PFA.

I tried some selections but I can't reproduce the '-2'.

Re: Avg time between events - avg aggr above problem

Hakisback — Fri, 19 Apr 2019 14:40:32 GMT

Thanks for the reply but it doesn't work for me

Enclosed all my data you will see that the calculation is not working

Thanks for your help

Re: Avg time between events - avg aggr above problem

rubenmarin — Fri, 19 Apr 2019 17:53:47 GMT

Hi, using structured parameter to sort date dimension in aggr seems to work:
=avg(aggr(date_passage-above(date_passage),idrcu_visite,(date_passage,(NUMERIC, ASCENDING))))

For purchases:
=num(avg(aggr(date_achat -above(date_achat),idrcu_achat,(date_achat,(NUMERIC, ASCENDING))))*24*60,'# ##0')&' minutes'

Re: Avg time between events - avg aggr above problem

Hakisback — Tue, 23 Apr 2019 07:42:06 GMT

This is it !

Thank you