Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
RSvebeck
Specialist
Specialist

Set Analysis do not match my previous IF expression?

Hi all

I cloned a table chart with and expression based on "IF" and converted the expression to Set Analysis instead.

Original expression:

Sum(
{1<myDate={$(vMyPeriod)},
myGroup < {96},
myCat < {90},
Salestype = {"External"},
CashType = {"Cash"}
>} Qty)


Replaced with this set expression:

Sum(IF
(
myDate = vMyPeriod
AND myGroup < 96
AND myCat < 90
AND Salestype = 'External'
AND CashType = 'Cash'
,Qty))


But when I compare the numbers of the two tables, the numbers are "slighly" different.

Is my SET expression wrong or do I have corrupt data?

I have made NO selections in my document.

Best Regards,

Robert Svebeck

Svebeck Consulting AB
1 Solution

Accepted Solutions
swuehl
MVP
MVP

Robert, I assume you mixed up the original and replaced expressions, right?

So, as set expression, I would suggest something like:

Sum(
{1<myDate={$(vMyPeriod)},
myGroup = {"<96"},
myCat = {"<90"},
Salestype = {'External'},
CashType = {'Cash'}
>} Qty)

Note how I changed the set modifiers to search terms for myGroup and myCat. This set expression should be even constant with regard to changed selections, except for the vMyPeriod maybe (because you are using set identifier 1), right?

Hope this helps,

Stefan

View solution in original post

7 Replies
swuehl
MVP
MVP

Robert, I assume you mixed up the original and replaced expressions, right?

So, as set expression, I would suggest something like:

Sum(
{1<myDate={$(vMyPeriod)},
myGroup = {"<96"},
myCat = {"<90"},
Salestype = {'External'},
CashType = {'Cash'}
>} Qty)

Note how I changed the set modifiers to search terms for myGroup and myCat. This set expression should be even constant with regard to changed selections, except for the vMyPeriod maybe (because you are using set identifier 1), right?

Hope this helps,

Stefan

johnw
Champion III
Champion III

In addition to what Stefan said, an if() statement respects existing selections, while a {1} set and an = in set analysis does not.  You're overriding everything instead of behaving like an if().  That'll work when you make no selections, but then return different results when you DO make selections.  I assume you want to match the if() in all cases.  So get rid of the {1} set, and use *= for your field checks to intersect with current selections instead of overriding.

sum({<myDate*={$(vMyPeriod)}

     ,myGroup*={"<96"}

     ,myCat*={"<90"}

     ,Salestype*={'External'}

     ,CashType*={'Cash'}

     >} Qty)

This expression will NOT be constant when you change selections, but it isn't intended to be.  It's intended to match the if().  Hopefully I got it right.

RSvebeck
Specialist
Specialist
Author

Hi guys,

Thanks for your help! I know have the same numbers in both tables.

I am aware that the SET and IF are different when it comes to making selections. That is the reason why I desided to change method from IF to SET, since I wanted the calculation to be selection independent.

That is also why I was confused that I got different result even though I made sure I had no selection made before comparing the two tables.

The solution was to put < inside brackets:

from my code: myCat < {90}, to your code: myCat = {"<90"}

But I still do not really understand the difference between these two methods. What was beeing calculated with my code, since the result was almost only 1% difference....?

John is suggesting to put *= after all fields, when I try that - there is no difference in the result,  it is equally aqurate to what Stefan suggested - so John: why use the *= instead of just = ? Is there a difference between those two? (myCat = {"<90"} and myCat *= {"<90"})

Regards, Robert

Svebeck Consulting AB
swuehl
MVP
MVP

Robert, you just can't use a comparison operator like < as field selection assignment operator in your set modifier, you need an equal sign here (kind of logical, it's kind of value assignment to a field name, like a selection, isn't it?).

You may use an implicite set operator like *=, +=, -=, /= though, this is just a short form, e.g.

<Year += {2007, 2008}> is equivalent to <Year = Year +  {2007, 2008}>

So, if you need to retrieve your field values by comparison to a value instead of explicitely stating same as element list, you need a search expression. The search expression needs to be enclosed in double quotes, so in total you get something like <myCat = {"<90"}> as your set modifier.

Hope this clarified your first question. I' not sure, but I think if you write it like <myCat < {90}> this invalids the complete or part of your set expression, so I think you get a result equal to removing  the set modifiers from your expression. Depending on your selection, this might be close to your expected result, but could be also miles off.

And coming to your second question, I believe there should be a difference - if you do make a selection and your selection is different from the one defined in your set expression.

Not sure why you don't see a difference. Could you post your current complete expression? Or a small sample app that shows the issue?

Regards,

Stefan

johnw
Champion III
Champion III

Robert Svebeck wrote:

Is there a difference between those two? (myCat = {"<90"} and myCat *= {"<90"})

Yes, as I was trying to say, = is an override, while *= is intersection.  So let's take an example.  Let's say you have a field Product with values A, B, C and D.  Let's say you select A and B. 

{<Product={'B','C'}>} returns B and C, because it overrides your selections.
{<Product*={'B','C'}>} returns only B, because it intersects your selections.

An if() behaves like *=.

However, it sounds like you do want to override, so stick with what Stefan said.

RSvebeck
Specialist
Specialist
Author

Again, thank you very very much for your answers.

I've understood a lot now about set that that I was previously unaware of, and at the moment my document is showing my just what I want and how I want!

Best Regards, Robert Svebeck

Svebeck Consulting AB
IAMDV
Luminary Alumni
Luminary Alumni

Thank you all! I have learnt some great tips from this thread.

Cheers - DV