Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

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

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'

View solution in original post

4 Replies

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

Partner
Partner

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

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'

View solution in original post

Partner
Partner

This is it !

 

Thank you