topic Page visits per day -> wrong values with set analysis in QlikView

Page visits per day -> wrong values with set analysis

rigosong — Sat, 16 Nov 2024 04:58:01 GMT

Hello,

with my application I'm reading the log files of my proxy and I want to show a table which tells me how many people (IP addresses) have visited my site more or less than 20 times per day.

Currently I have the following table:

Left table are the page visits by IP per day and at the right table I'm trying to show how many users have visited the site more or less than 20 times per day.

As you can see by the 7th of May my calculation doesn't work because it says there are 0 IPs which have visited the site less than 20 times and 2 IPs which have visited the site more than 20 times. However, at the left table you can see that on the 7th of May 3 different IPs visited the site and 2 of them have visited it more than 20 times and one IP less than 20 times. So actually the right table should say 1 and 2 instead of 0 and 2 for this day.

However if I choose only one day, then it works fine:

My expressions for the calculation:

less than 20:

=count({<IP *= {"=count(Date)<20"}>}distinct IP)

more than 20:

=count({<IP *= {"=count(Date)>20"}>}distinct IP)

Any ideas what I'm doing wrong?

Re: Page visits per day -> wrong values with set analysis

sunny_talwar — Thu, 13 Dec 2018 17:53:13 GMT

There is two way to solve this...

1) Create a new field which includes Date and IP like this

Hash128(IP&Date) as IP_Date_Key

and now try this

less than 20:

=count({<IP_Date_Key *= {"=count(Date) < 20"}>} DISTINCT IP)

more than 20:

=count({<IP_Date_Key *= {"=count(Date) > 20"}>} DISTINCT IP)

2) Use Aggr() with if statement instead of set analysis

less than 20:

=Count(Aggr(If(Count(Date) < 20, IP), IP, Date))

more than 20:

=Count(Aggr(If(Count(Date) > 20, IP), IP, Date))

Re: Page visits per day -> wrong values with set analysis

rigosong — Thu, 13 Dec 2018 18:03:41 GMT

Thank you Sunny. This works perfectly fine 🙂