Discussion Board for collaboration related to Creating Analytics for QlikView.
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
Go to Solution.
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'.
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
This is it !