Hey Guys - this is Mike Tarallo, creator of this video.This video may be from 2015 however it is still very relevant - and we still get many positive comments on it. Please note we have added a few new ways in our expression builder UI to insert Set Analysis statements for you. Be sure to explore the expression editor / builder and take advantage of the INSERT capability when building expressions. You can insert current selections and even bookmarks into your statements. Check out this link to learn more: help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Expressions/expression-editor.htm
Our pleasure - just let us know if you need help with anything, we will do our best to answer or guide you. - BTW you may like this series as well: - th-cam.com/play/PLW1uf5CQ_gSq6klZOXSpKQ0afQFJyqqEh.html - if you got 60 seconds - you got time for SaaS in 60 - Regards, Mike Tarallo - Qlik
Hi Michael, At the 5:04 mark, doesn't the word Germany need a Curly Bracket after the equals sign but before the quote in front of the "Germany"}? This is shown at 4:53 - Sum({}Sales). This looks to be "fixed" or also "incorrect" at 5:30 as well. Just trying to clarify as I am new to this BI stuff Thanks
Hi Randy - yes the 5:04 mark is a mistake - but the 5:30 mark is correct - originally when this was posted in 2015 - I had an annotation here to show the correct syntax - but because TH-cam so brilliantly removed annotations - this is no longer there. Thanks for your question and please be sure to join the Qlik Community!
Hi Jason - happy to help - can you believe it? over 200K views and it's been over 7 years since I made this video and it's still helping many people. Note we are adding more and more features in the UI to support Set-Analysis-like calculations. These features can be found in different places - like the Master Items Library (Calendar Measures) as well in the Expressions UI and even a new filter chart capability in the SaaS (Cloud-based Qlik Sense) - simplified UI. If you have any more questions - please feel free to ask - but also please make sure you become a member of the Qlik Community where 1000s of members are happy to help. Stay well - Mike T
+Akshay Rane They are not the same. 1-$ will take all data and then deduct the set which you define in set analysis (which is an intersection of your selections - $ - and filters applied by set modifiers). $-1 will take your selection and then deduct the set defined in set analysis (only but this time it is an intersection of all data and filters applied by set modifiers). So basicaly the difference is similar to that of difference between 10-1 and 1-10. And, to answer your first question, I think the expression should be edited to 1-$ as you suggest.
Hi , I hope you can help me: smileysad: In summary I have a database with some columns "colon cancer, lung cancer, etc" and in the rows the names of districts. The problem is that I want to create a filter that group all the variables (columns) and display 1 by 1 according with the selection and that this selection changes the TREEMAP graph. I leave you an image of how I currently have my dashboard.
Very good one. Just one thing at 4.09 you were talking about exclusion current selection 1-$ but you wrote $-1 in expression. Am i wrong in my understanding? Many thanks.
Hi, does this allow cross-reference across 3 data sets? for example, i have 3 spreadsheets of data: 1. A list of employees names from Team A only (not the full list of employees) 2. A list of only 10 product names (not the full list of products) 3. A list that shows the quantity of all product names sold by all employees (note: this 3rd list does not have a column that defines whether this employee is from which team, and the product names is not limited to just the 10 product in the 2nd list). Can a chart be done by displaying the quantity of product sold by employees, by cross referencing the employee name list of Team A, and also cross-reference the 10 product name list... so that it only displays quantity sold of the 10 products by employees from Team A only?
Thank you for the video, I have only just started to understand set analysis. Really excited about what I can use it for particularly as I have access to multiple datasets from hospitals, alcohol and drug services, Medicare (Australia), and various others. My question is how could I use the search function to return the admissions to hospital for a week outside of normal hours ie; Mon-Fri 8am to 8pm, Sat 8am to 5pm. I want to sum all the days individually to display to hours with the largest percentage of numbers?
Hi Darren - glad you liked it. Yes Set Analysis is very useful and fun to use once you get the hang of it, and once you start using it, you may realize it is not entirely that difficult. IN regards to your question, would you mind posting it here: community.qlik.com - we are better equipped to answer you and you will reach out to a greater variety of experts.
Thanks for the response. And I will post my question in the community link you have provided and give a little bit more detail. I am very keen to watch the video on variables as I find your short tutorials very helpful.
Hi Ale Ram - best thing to do is use the Qlik Community - community.qlik.com to ask these types of questions as I may not be able to get to these in a timely manner. - In short you can use a -= in the Set Analysis statement to exclude values from the set - if you are referring to something else - we will need to understand this a bit more. Because you can also use {1} to ignore selections - so the measure stays the same only showing the measure for the set statement - and will not change when selections are made. If you would like to continue this conversation, please open a discussion thread in the Qlik community where myself and many other Qlik enthusiast, employees, and partners will be happy to help. :-)
Hi Mario - there are a number of uses for the $ depending on where you are using it - if you are using it in the beginning of the set expression it tells the expression to respect selections -so when you select values from a list - the expression will show the measure for those selected values. If it is used in a comparison with a field and value it is used to tell the parser that the following syntax being used in the set analysis expression is not a literal and is an actual expression to be evaluated. So it allows you to use functions to evaluate a value that is to be compared in the set analysis expression. Look up dollar sign expansion or dollar expansion or $ expansion. Please check the online help for more info.
P Kumar Hello - check out these series of videos here - community.qlik.com/docs/DOC-6932 - your question is answered specifically in this video: community.qlik.com/docs/DOC-6746
![KRK - ชนิสรา Ft.WanMai , N/A [Official MV] Prod. By Sakarin](http://i.ytimg.com/vi/C-WMwwYobcU/mqdefault.jpg)
Hey Guys - this is Mike Tarallo, creator of this video.This video may be from 2015 however it is still very relevant - and we still get many positive comments on it. Please note we have added a few new ways in our expression builder UI to insert Set Analysis statements for you. Be sure to explore the expression editor / builder and take advantage of the INSERT capability when building expressions. You can insert current selections and even bookmarks into your statements. Check out this link to learn more: help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Expressions/expression-editor.htm
Great video! I use Power BI a lot and was curious on Qlik. Seems to be very powerful tool! Keep posting. Learning so much from your channel. Thanks.
Our pleasure - just let us know if you need help with anything, we will do our best to answer or guide you. - BTW you may like this series as well: - th-cam.com/play/PLW1uf5CQ_gSq6klZOXSpKQ0afQFJyqqEh.html - if you got 60 seconds - you got time for SaaS in 60 - Regards, Mike Tarallo - Qlik
great basics explanation!!!
Brilliant video. Instructive and easy-to-understand.
Easy to understand
Hi Michael, At the 5:04 mark, doesn't the word Germany need a Curly Bracket after the equals sign but before the quote in front of the "Germany"}? This is shown at 4:53 - Sum({}Sales). This looks to be "fixed" or also "incorrect" at 5:30 as well. Just trying to clarify as I am new to this BI stuff Thanks
Hi Randy - yes the 5:04 mark is a mistake - but the 5:30 mark is correct - originally when this was posted in 2015 - I had an annotation here to show the correct syntax - but because TH-cam so brilliantly removed annotations - this is no longer there. Thanks for your question and please be sure to join the Qlik Community!
Thank you for this!
Hi Jason - happy to help - can you believe it? over 200K views and it's been over 7 years since I made this video and it's still helping many people. Note we are adding more and more features in the UI to support Set-Analysis-like calculations. These features can be found in different places - like the Master Items Library (Calendar Measures) as well in the Expressions UI and even a new filter chart capability in the SaaS (Cloud-based Qlik Sense) - simplified UI. If you have any more questions - please feel free to ask - but also please make sure you become a member of the Qlik Community where 1000s of members are happy to help. Stay well - Mike T
Nice one
Shouldn't the expression be edited to '1-$' at 4:10 instead of '$-1'? Or do they both mean the same?
+Akshay Rane They are not the same. 1-$ will take all data and then deduct the set which you define in set analysis (which is an intersection of your selections - $ - and filters applied by set modifiers). $-1 will take your selection and then deduct the set defined in set analysis (only but this time it is an intersection of all data and filters applied by set modifiers).
So basicaly the difference is similar to that of difference between 10-1 and 1-10. And, to answer your first question, I think the expression should be edited to 1-$ as you suggest.
Hi , I hope you can help me: smileysad:
In summary I have a database with some columns "colon cancer, lung cancer, etc" and in the rows the names of districts. The problem is that I want to create a filter that group all the variables (columns) and display 1 by 1 according with the selection and that this selection changes the TREEMAP graph. I leave you an image of how I currently have my dashboard.
Very good one. Just one thing at 4.09 you were talking about exclusion current selection 1-$ but you wrote $-1 in expression. Am i wrong in my understanding? Many thanks.
I think you are correct.
Hi, does this allow cross-reference across 3 data sets?
for example, i have 3 spreadsheets of data:
1. A list of employees names from Team A only (not the full list of employees)
2. A list of only 10 product names (not the full list of products)
3. A list that shows the quantity of all product names sold by all employees (note: this 3rd list does not have a column that defines whether this employee is from which team, and the product names is not limited to just the 10 product in the 2nd list).
Can a chart be done by displaying the quantity of product sold by employees, by cross referencing the employee name list of Team A, and also cross-reference the 10 product name list... so that it only displays quantity sold of the 10 products by employees from Team A only?
Thank you for the video, I have only just started to understand set analysis. Really excited about what I can use it for particularly as I have access to multiple datasets from hospitals, alcohol and drug services, Medicare (Australia), and various others. My question is how could I use the search function to return the admissions to hospital for a week outside of normal hours ie; Mon-Fri 8am to 8pm, Sat 8am to 5pm. I want to sum all the days individually to display to hours with the largest percentage of numbers?
Hi Darren - glad you liked it. Yes Set Analysis is very useful and fun to use once you get the hang of it, and once you start using it, you may realize it is not entirely that difficult. IN regards to your question, would you mind posting it here: community.qlik.com - we are better equipped to answer you and you will reach out to a greater variety of experts.
Thanks for the response. And I will post my question in the community link you have provided and give a little bit more detail. I am very keen to watch the video on variables as I find your short tutorials very helpful.
Hi, how do you exclude values (alpha and or numerical) on a selection of a Text Object?
Hi Ale Ram - best thing to do is use the Qlik Community - community.qlik.com to ask these types of questions as I may not be able to get to these in a timely manner. - In short you can use a -= in the Set Analysis statement to exclude values from the set - if you are referring to something else - we will need to understand this a bit more. Because you can also use {1} to ignore selections - so the measure stays the same only showing the measure for the set statement - and will not change when selections are made. If you would like to continue this conversation, please open a discussion thread in the Qlik community where myself and many other Qlik enthusiast, employees, and partners will be happy to help. :-)
Its good instruction..
Whats the dollar sign for?
Hi Mario - there are a number of uses for the $ depending on where you are using it - if you are using it in the beginning of the set expression it tells the expression to respect selections -so when you select values from a list - the expression will show the measure for those selected values. If it is used in a comparison with a field and value it is used to tell the parser that the following syntax being used in the set analysis expression is not a literal and is an actual expression to be evaluated. So it allows you to use functions to evaluate a value that is to be compared in the set analysis expression. Look up dollar sign expansion or dollar expansion or $ expansion. Please check the online help for more info.
How to create reusable measure in qlikview and where we need to create this.
P Kumar Hello - check out these series of videos here - community.qlik.com/docs/DOC-6932 - your question is answered specifically in this video: community.qlik.com/docs/DOC-6746
thank your video.Its good instruction