Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist III
Specialist III

A Primer on Set Analysis Blog Post

I cant post a comment on Henric's last blog post

A Primer on Set Analysis

And as no comments are posted. Is this a bug or are comments not allowed now

Your content could not be saved due to an error. You may have been logged out. If this problem persists please contact your system administrator. Click here to refresh this page.

A Primer on Set Analysis


Set analysis is one of the more complex things you can define in QlikView or Qlik Sense.

Agree.

I still use 'IF' when I can at times because its simple whereas Set analysis is confusing  when $'s and double quotes are used .(why its been done this way I'm not too sure)

For example

Sum( {$<Date={"<=$(=Max(Date))"}>} Sales)

compared to Sum(if (Date <= Max (Date) , Sales))


One is straight forward. The other very confusing (this should be simplified by Qlik to

Sum( {$<Date <=  {Max(Date)}>} Sales)


A question though

Is *= used to allow drill down

"If" allows drill down (for a filter selection contained in the expression)


eg  sum ( if Cust_Num = 1234 or Cust_num = 1235, Sales)

Does Sales ({<Cust_Num *= {1234,1235}>} Sales


only show sales for 1234 if the user makes a selection of 1234 (it seems to in a number of examples I have tested it on)

Thanks

17 Replies
Highlighted

Yes, I know it has a lot of functionality (altern states, bookmarks, the "1-$", E(), modifier operators...), what I tried to say is:

If you have something working with an 'if' solution, sometimes you look for a 'set analysis' solution because of performance.

I take this ocassion to thank you for all the very good posts on design blog.

Highlighted
Specialist III
Specialist III

No I have tried to convert all of my 'IF' expressions to set analysis

But with some of the more complex ones (using > etc. I didn't because I could not get them to work. Whereas 'IF'  worked as required

BUT one thing I did not like with set analysis (as opposed to if) is the lack of a drill down feature

Example

Sum( {$<Date =  {Max(Date)}>} Sales)

If I wanted to filter (make  a selection) of Date = 10/01/2015. It wouldn't work because date is already set to Max(Date)

But if

Sum( {$<Date *=  {Max(Date)}>} Sales)

is used it seems to overcome this issue

I have only used this in a few examples recently (once I found out about  *=) but am unsure if I should be using it in this way.

Hope this makes sense

Highlighted
Partner
Partner

Hi Henric,

great post, as usual.

Did i well understand ? Simple quotes should never be used with set analysis.

If i want to make a simple filter on a text field, i must write :

Sum( {$<Country="France">} Sales) and not Sum( {$<Country='France'>} Sales), correct ?

as you wrote

Single quotes should denote literals, i.e. explicit field values.

i'm a bit confused.

Best regards,

Johann

Highlighted

In your case, both will probably work. The single quotes should be a case-sensitive match and the double should be a case-insensitive search.

But as soon as you insert wild cards it will matter.

Consider the following: {$<Country={"Fr*"}>} vs {$<Country={'Fr*'}>}. Here the "Fr*" means a search and the * is a wild card. But since single quotes is a literal, 'Fr*' will match the exact combination of 'Fr*', i.e. three characters where the last is an asterisk.

HIC

Highlighted
Champion III
Champion III

Hi Johann,

I"m not Henric but can answer this one...

'France' is a filed value, hence it must be in the single quotes.  You second example is almost correct.  It should be:


Sum({$<Country={'France'}>} Sales)

Regards,

Michael

Highlighted
Partner
Partner

ok, thank you very much for your answer !

so is there any performance boost to use single quotes when you actually want to use literals ? ie.

Sum{<MyField={'<field value here'}>} value)

instead of

Sum{<MyField={"<field value here"}>} value)

best regards.

Johann

Highlighted
Partner
Partner

Thanks michael !

Highlighted
Specialist III
Specialist III

As an example

This gives the correct result

Count

({< FSRNum2 = {1},CalTStdPM = {STD} ,Group = {Calls} >}
distinct (if ( ((Call_RDate)) >= ( VisMinStartRef)
,CallSerial)))

But this doesn't


Count     ({$< FSRNum2 = {1},CalTStdPM = {STD} ,Group = {Calls},
Call_RDate = {" >= VisMinStartRef " }>}     distinct CallSerial)

I can not work out why?