topic Re: Count Users with more than x ads in QlikView

Count Users with more than x ads

nigel987 — Mon, 21 Mar 2016 15:39:52 GMT

Hi,

I want to count users, who have posted more than 1 ad. I used following expression, which worked fine:

=Count({$<LoginID={"=Count(DISTINCT AdID) > 1"}>}DISTINCT LoginID)

But now I want to make a pivot chart with the dimensions: MonthPost (month of the posted ad) and Category (category of the posted ad).

The MonthPost works as expected: only users who posted more than 1 ad in the month are counted.

But the category counts all users which had at least one 1, no matter which category it was.

Could someone give me a hint how I could solve this? My assumption is that in my expression first the part "=Count(DISTINCT AdID) > 1" .

Re: Count Users with more than x ads

sunny_talwar — Mon, 21 Mar 2016 15:42:24 GMT

Do you have a sample qvw file you can share?

Re: Count Users with more than x ads

Chanty4u — Mon, 21 Mar 2016 15:42:31 GMT

what is ur expected?

Re: Count Users with more than x ads

nigel987 — Mon, 21 Mar 2016 15:52:19 GMT

Hi,

in my example I have one user, who has several ads. I use the dimensions Month and Category.

As I only want to count users with more than 1 ad, I would expect that in March the category Gesundheit also has no count.

Re: Count Users with more than x ads

Digvijay_Singh — Mon, 21 Mar 2016 15:57:20 GMT

Do you want to count no of add per month per category by same user? If you have month and category dimensions in your pivot table then your expressions need to respect chart dimensions.

Re: Count Users with more than x ads

Chanty4u — Mon, 21 Mar 2016 15:57:54 GMT

this will give u the same result ryt

=Count(DISTINCT AdID) > 1

so if u hve more than 1 ad it will display.

in march u dnt hve add it wil nt disply

Re: Count Users with more than x ads

nigel987 — Mon, 21 Mar 2016 16:00:20 GMT

Not exactly, I want to count number of users which have a number of ad greater than 1 per month per category. In my app there is only one user. But I would expect that in March the user would not be counted in the cateogory Gesundheit as he only has 1 ad in this month in this category.

Re: Count Users with more than x ads

sunny_talwar — Mon, 21 Mar 2016 16:00:27 GMT

May be try this:

=Count(DISTINCT If(Aggr(Count(DISTINCT AdID), LoginID, Hauptkategorie, AdPublishedMonat) > 1, LoginID))

Re: Count Users with more than x ads

nigel987 — Mon, 21 Mar 2016 16:17:04 GMT

Thank you Sunny, this is exactly what I was looking for! Didn't think of using an if clause with an aggr in the count function.Thanks!