Wow! I think it’s about time to post something new since the post about Packt has been the latest post for two months now. I’ve been quite busy lately trying to launch new initiatives in the company and I’ve recently decided to write a follow-up book about more advanced QlikView data visualization and analysis. If you have any requests on what you would like to see in this new book, please send me your comments
As I start investigating the topics and writing, I occasionally stumble upon tips that I’ve forgotten about. Have you ever done something like the following to respect the current selection of a field in set analysis while ignoring the selections in other fields?
=num(sum({1<[MasterCalendar Year]={$(=concat([MasterCalendar Year],’,’))}>} Amount),’#,##0′)
The dollar-sign expansion will generate a list of the years selected and create the following expression.
=num(sum({1<[MasterCalendar Year]={2013,2014}>} Amount),’#,##0′)
If you need to put quotes around each value because the list may contain strings with spaces, the expression becomes even more complicated.
=num(sum({1<[MasterCalendar Year]={$(=chr(39) & concat([MasterCalendar Year],chr(39) &’,’ & chr(39))& chr(39))}>} Amount),’#,##0′)
So instead of creating such a hideous expression, why not just use the following?
=num(sum({1<[MasterCalendar Year]=$::[MasterCalendar Year]>} Amount),’#,##0′)
I stole this idea from the examples Qlik posted when it introduced alternate states. Note that the opposite syntax does not work.
=num(sum({$<[MasterCalendar Year]=1::[MasterCalendar Year]>} Amount),’#,##0′)
Rather the opposite syntax is something we commonly use in every set analysis.
=num(sum({$<[MasterCalendar Year]=>} Amount),’#,##0′)
Again, if you have any topic you would like to see detailed the follow-up to Learning QlikView Data Visualization, please send me your comments.
Hope you see you around,
Pover
Another solution to this problem is to user P().
=num(sum({1} Amount),’#,##0′)
I noticed that my comment was stripped of anything inside chevrons brackets. Replace the ¤ below with left and right chevrons and it will be correct.
=num(sum({1 ¤ [MasterCalendar Year]=P([MasterCalendar Year]]) ¤} Amount),’#,##0′)