topic Re: Using Aggr function within set analysis in QlikView

Using Aggr function within set analysis

SaveonHailey — Fri, 01 Nov 2019 21:20:24 GMT

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

Re: Using Aggr function within set analysis

SaveonHailey — Fri, 01 Nov 2019 21:36:51 GMT

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

Re: Using Aggr function within set analysis

Vegar — Sun, 03 Nov 2019 06:55:27 GMT

Try something like this.

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

Re: Using Aggr function within set analysis

SaveonHailey — Mon, 04 Nov 2019 01:42:12 GMT

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

Re: Using Aggr function within set analysis

Vegar — Mon, 04 Nov 2019 08:47:23 GMT

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

Re: Using Aggr function within set analysis

simrankaur — Mon, 04 Nov 2019 14:22:27 GMT

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

Re: Using Aggr function within set analysis

Brett_Bleess — Thu, 07 Nov 2019 21:01:19 GMT

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