https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Se...
https://www.youtube.com/watch?v=hxsKnftVECk
Used for set of groups mostly used in aggregate function like sum (year) used
sales of current year VS last year
<Year = { “$(=max(Year)) ” }> & <Year = { “$(=max(Year)-1) ” }>
*sum ({$ < year= {2012} >} sales): The sum of sales for the current selections for the year 2012.
*sum ({alternate state 1 < year = {2012}>} sales): The sum of sales for the year 2012 and the selections made in list boxes designated as an alternate state named alternate state 1.
*sum ({ $ < year= year – {2011} >} sales): The sum of the sales for the selected year (s) (noted by $), but excluding 2011.
*sum ( {$ < product = {“ *”} >} sales): The sum of the sales for all products (wild card character denoting all) for the selections.
*sum ({< product = >} sales): The sum of sales for the selected fields, but ignoring the product field selection.
*sum ({$ < product = { }>} sales): The sum of sales for the selected fields, but for anything not associated with the selection in the product field.
*sum ($ } sales): Using the dollar sign expansion for the v year variable, this is the sum of sales for the year specified in the variable. An example of this is if the v year variable was set to the expression
= year (today ()).
*sum ( { 1} sales): the sum of sales for all year in the full data set (1) for the years starting with 20(wild card*), and 1999, but excluding 2001 (minus exclusion operator before 2001).
*sum ({ $} sales): the sum of sales for all selections for a product containing the string laptop (by using the wild card * around the string laptop).
*sum ({ $ } sales): the sum of sales for the selected year, plus the years 2012 and 2013 (this is a union operation).
*sum ({ $ } sales): the sum of sales for the years 2011 and 2012, in the Europe zone.
*sum ({ $ < customer= (p ( { 1< product category= {“ instruments” } >}))>} sales) : the p element here indicates all possible data. The other option for an element is E, which is excluded and the opposite effect of possible. This is the sum of sales for all customers who ordered any products of the instrument product category. To omit selections made against the field, the moderator can be left empty.
Refer below example,
1) =Count({$<Type={'Accepted Lead'}>+<Type={'Instructed Lead'},ProductId-={'5'}>}TicketId)
above set expression will counting where TicketId is Type 'Accepted Lead' OR 'Instructed Lead':
3) Count(if(Completion_Date <= Due_Date and (Source = 'Assignments' or (Source = 'Tasks' and TASK_TYPE = 'Product')),Task_ID_New))
if we convert above expression into Set analysis it will be look like below,
count({<Source={'Assignments'},ID={"=Completion_Date<=Due_Date"}>+<Source={'Tasks'},TASK_TYPE={'Product'},ID={"=Completion_Date<=Due_Date"}>} Task_ID_New)
Counting Customers who ordered Multiple Products
Count({$<Customer={"=Count(distinct Product)>1"}>} distinct Customer )
Set Analysis – New and Lost Customers with Nested Advanced Search
The only acceptable exception to this rule would be when we use nested Set Analysis. Nested Set Analysis is when we use an advanced search to filter a field that itself is also filtered by Set Analysis. For example,
<Customer = {"=sum( {$<Ingredient={'*Garlic*'}>} Sales)>10000"}>
will filter the customers that have sales greater than ten thousand for products containing garlic.
One real world application of nested Set Analysis helps us define the business rules that define the indicators for won and lost customers, which are key indicators in any sales application. Our example business rules are the following:
- New customers are those that purchased for the first time ever anything within the previous 30 days.
- Lost customers are those that have purchased historically within the last 120 days, but have not purchased anything within the previous 60 days.
- The indicators can be viewed for previous months and not just the current month
- Only consider positive sales transactions (no returns)
Our data model includes a master calendar with Month, Year, YearMonth, and OrderDate; a customer catalog; and a fact table with a Sales field.
In short, the expression is going to calculate the first and last time each customer made a purchase before the date selected by the user and see if it falls within the range of days defined in the business rules we previously defined.
If we were to calculate the first time a customer made a purchase before the date selected by the user we would use the following expression,
min({$<Sales={'>0'}, Year=, Month=, YearMonth=, OrderDate={'<=$(=max(OrderDate))'}>}
OrderDate)
and we only need to change the min to a max in order to calculate the last time a customer made a purchase,
max({$<Sales={'>0'}, Year=, Month=, YearMonth=, OrderDate={'<=$(=max(OrderDate))'}>}
OrderDate)
Notice we used single quotes ( ‘ ) in the search expressions found the in the Set Analysis included in the previous formulas. Now, we take a simple count distinct,
count(distinct Customer)
and add the previous formulas that calculated the first and last times a purchase was made and compare it to the maximum date selected by the user,
=max(OrderDate)
in an advanced search in our Set Analysis.
If the first date a purchase was made is less the 30 days it is considered a new customer, and if the last date a purchase was made is between 60 and 120 days it is considered a lost customer. Our resulting expressions are as follows.
New Customers
count({$<Year=, Month=, YearMonth=,Customer={"=min({$<Sales={'>0'}, Year=, Month=,
YearMonth=, OrderDate={'<=$(=max(OrderDate))'}>} OrderDate)>=$(=max(OrderDate)-30)"}>}
distinct Customer)
Lost Customers
count({$<Year=, Month=, YearMonth=,Customer={"=max({$<Sales={'>0'}, Year=, Month=,
YearMonth=, OrderDate={'<=$(=max(OrderDate))'}>} OrderDate)< $(=max(OrderDate)-60)
and max({$<Sales={'>0'}, Year=, Month=,
YearMonth=, OrderDate={'<=$(=max(OrderDate))'}>} OrderDate)>= $(=max(OrderDate)-120)"}>}
distinct Customer)
Notice we used double quotes ( “ ) to surround the nested advanced search expression. Additionally, it is important to remember that we have to repeat ignored fields (i.e. Year, Month and YearMonth) in both the outside and nested Set Analysis.
http://poverconsulting.com/2014/03/04/set-analysis-nested-advanced-search/
hope this help
Thanks
Vikas
Hope this resolve your issue.
If the issue is solved please mark the answer with Accept as Solution & like it.
If you want to go quickly, go alone. If you want to go far, go together.
