topic Counting recent occurrences in QlikView

Counting recent occurrences

Tue, 14 Dec 2010 11:08:35 GMT

We are currently evaluating QlikView and are trying to model our metrics in QlikView. I'm trying to see how far I can get by using the data directly from our production slave database.

It's coming along quite well, but I have hit one stumbling block that I have been unable to solve. We are logging user logins to our site, and would like to measure how many users use our product on a daily, weekly and monthly basis. To do this we want for each day to calculate how many users have logged in in the last 30 days, at least 4 times in the last 30 days and at least 16 times in the last 30 days. I can easily model the first one by accumulating 30 steps back. I am however unsure how I should model the other two.

Any hints on how this should be done? The data is simply a table of user ids and the dates they logged in.

Thanks for your help,

Christian, Podio

Counting recent occurrences

maneshkhottcpl — Tue, 14 Dec 2010 12:17:16 GMT

Hi,

It is very easy to calculate using frontend scripting in chart. U can use count(If(TransactionDate<=today() and TransactionDate>=today()-30,Login)

for any query u can send me the application.

Counting recent occurrences

Tue, 14 Dec 2010 12:42:39 GMT

Thanks for your help.

I have the table as (user_id, login_date), and have used login_date as the dimension. If I use your example I would always test against today, while I need to test against the login, so I can get the changes over time, not just the current picture. I am I misunderstanding something?

Counting recent occurrences

martinpohl — Tue, 14 Dec 2010 13:37:03 GMT

Hello Christian

try this

Number of users logged in last 30 days

count({1<logindate={">=$(=Date((Today()-30), 'DD.MM.YYYY'))"}>}distinct User)

number of user logged in last 30 days but at least 4 times

count({$< User={'=count({<logindate={">=$(=(Date((Today()-30), 'DD.MM.YYYY')))"}>}logindate)>3'}>} distinct User)

number of user logged in last 30 days but at least 16 times

count({$< User={'=count({<logindate={">=$(=(Date((Today()-30), 'DD.MM.YYYY')))"}>}logindate)>15'}>} distinct User)

although the formula editor is given an error it works 🙂