Sets vs if statements

[Anonymous]

Why does the following:

= Count({$<[PFI ID] = {'$(vPFI_blank)'}>} UniqueID)

returns a different result than:

= Count(if [PFI ID] = $(vPFI_blank), UniqueID)

The first returns the correct result while the second one doesn't.

-- Ben

[johnw]

I believe these are equivalent.  Neither matches your current expressions, but I believe are what you are attempting to do:

count({<[PFI ID]*={'$(vPFI_blank)'}>} UniqueID)
count(if([PFI ID]=vPFI_blank,UniqueID))

For the first expression, when you do [PFI ID]= in set analysis, this is an OVERRIDE.  It overrides your selections and picks ALL the values in the set.  Using if(), on the other hand is INTERSECTION.  You get the intersection of the selected values and the values in the if().  An if() cannot be converted to an override, but set analysis can be converted to an intersection by using *=.

For the second expression, you had a syntax error where you were missing parentheses on the if().  Also, keep in mind that $() is dollar sign expansion, and will literally insert the value into the expression.  If the value is something like Hello World, you'd end up with [PFI ID] = Hello World, which is a syntax error because string literals must be enclosed in single quotes.  You could enclose it in single quotes just like you did in the set analysis, but in the if(), I believe it is valid to simply remove the $() and check the variable value directly.

[Anonymous]

Hi, John.  Thanks for the prompt reply.  I was quickly typing the syntax I was using before but here is a bit more detail:

1) Nothing is selected, the data is broken down by month and year.

2) The formula:

     = Count({$<[PFI ID] = {'$(vPFI_blank)'}>} UniqueID)

     returns 3,112 (the correct answer)

3) The formula:

     =count( if([PFI ID] = vPFI_blank,UniqueID))

     returns 3,266 (an incorrect answer)

I've been fighting this for the past day.  Does it make any sense?  What am I not seeing?

Thanks again!

-- Ben

[johnw]

I'm not seeing it.  As long as nothing is selected, those look equivalent to me.  One issue would be if UniqueID is a field on more than one table, though I'd honestly then expect both expressions to return equally wrong results.  Can you post an example demonstrating the problem?

[Anonymous · Accepted solution]

Hi, John.  Just wanted to get back to you.  I ended up getting to the answer a different way.  I was building a stacked line chart with two lines - 1 for items with a PFI ID and the other for items without a PFI ID.  I was doing this by setting up metrics / expressions as noted above.  It turns out, however, that I could get the correct response just by using a simple count (UniqueID) as the expression and building a calculated dimension =if([PFI ID]=vPFI_blank, 'No PFI', 'w PFI').  This solved the problem and seems to perform a bit faster.

Anyhow, I just didn't want to leave this hanging.  I'm not sure what caused the earlier problem but, at least, my testing turned it up.  Thanks again for your help!

-- Ben