Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

SaveonHailey
New Contributor

Using Aggr function within set analysis

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

6 Replies
SaveonHailey
New Contributor

Re: Using Aggr function within set analysis

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))

Partner
Partner

Re: Using Aggr function within set analysis

Try something like this. 

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
SaveonHailey
New Contributor

Re: Using Aggr function within set analysis

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))

 

Partner
Partner

Re: Using Aggr function within set analysis

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])

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
simrankaur
New Contributor III

Re: Using Aggr function within set analysis

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

Digital Support
Digital Support

Re: Using Aggr function within set analysis

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

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.