Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Hakisback
New Contributor

Avg time between events - avg aggr above problem

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

Labels (3)
1 Solution

Accepted Solutions

Re: Avg time between events - avg aggr above problem

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'
4 Replies

Re: Avg time between events - avg aggr above problem

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

Hakisback
New Contributor

Re: Avg time between events - avg aggr above problem

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

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'
Hakisback
New Contributor

Re: Avg time between events - avg aggr above problem

This is it !

 

Thank you