Using Aggr function within set analysis

[SaveonHailey]

Our data set contains Household ID and Transaction Number, and I am trying to discover how many Households have more than 2 transactions within the last 4 months and I think I need to use the Aggr function to count the transactions by Household and then where ever this count is greater than 2 I need to count only those Households, so I am thinking I need to do something like this

count({$<aggr(count(distinct [Transaction Number]), [Household ID])>2}  [Household ID])

where [Transaction Number] and [Household ID] are fields in our data set.

but it is returning no values, where am I going wrong?

Thanks, Hailey

[SaveonHailey]

reply to my own post (not sure if this is correct)

I know I can get the result by using the if function as below but was trying to conserve resources by using set analysis

sum(if(aggr(Count(distinct [Transaction Number]),[Household ID],Family)>2,1,0))

[Vegar]

Try something like this. 

count({$<[Household ID]={"=count(distinct [Transaction Number])>2"} [Household ID])

[SaveonHailey]

That did not work. Maybe I need to clarify, I made a slight mistake in my original post.

Our item sales data set contains Household ID and Transaction Number and Item ID, and I am trying to discover how many Households have purchased each item more than 2 transactions within a selected time fram and I think I need to use the Aggr function to count the transactions by Household by Item ID and then whereever this count is greater than 2, I need to count only those Households, so I was thinking I need to do something like this, but this does not return any result and I think my syntax for the aggr function within the set analysis is wrong.

count({$<aggr(count(distinct [Transaction Number]), [Household ID], [Item ID])>2}  [Household ID])

where [Transaction Number] and [Household ID] and [Item ID] are fields in our data set.

I am new to set analysis and struggle with the syntax, particularly when using a function within the set analysis.

I can get the result using an if statement like below, but was under the assumption that using set analysis instead of if statements would free up resources and run faster.

sum(if(aggr(count(distinct [Transaction Number], [Household ID], [Item ID])>2,1,0))

[Vegar]

I made an adjustment to my expression and fixed a typo (I typed my previous answer on a mobile device)

count({$<[Household ID]={"=count(distinct [Transaction Number] &'|'&[Household ID] &'|'& [Item ID]  )>2"} >}[Household ID])

[simrankaur]

Try and use this:

here,

numDate =num(date)

and, vNumdate4monthsback=  num(today()-120)

=if(Count({<numDate = {">= $(vNumdate4monthsback)"}>}HouseholdID & '-' &  ItemID)>2,Count({<num = {">= $(numdate4monthsback)"}>}HouseholdID & '-' &  ItemID))

please revert if you have any more queries

[Brett_Bleess]

Hailey, have a Design Blog post that may be of some help too:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

If any of the recent posts helped, do not forgot to use the Accept as Solution button to mark any that worked to give credit and let others know they worked.

Regards,
Brett