Can't figure out why my set analysis / if expression is always false

[#1]

I'm trying to compare last years customer satisfaction with the previous year and display upward pyramid if true and pyramid down if the value is smaller. Year is a variable with values 2013 or 2014 attached to each observation.

=if(avg({$<Year={2014}>}[Customer satisfaction])>=avg({$<Year={2013}>}[Customer Satisfaction]), chr(9650),chr(9660))

The above if expression is always false.

avg({$<Year={2013}>}[Customer Satisfaction]) equals '-'

If I try '=Year=2014' and select 2014 from a field the expression is True.

avg({$<Year=>}[Customer Satisfaction]) works.

avg({$}Customer Satisfaction])  and selecting a year from Year etc. works.

I just can't figure out the problem. Thanks for the help.

[Gysbert_Wassenaar]

Are you using Year as a dimension in the object where you use that expression? If so, you need to realize that the sets of set analysis expressions are calculated at the chart level, not at the row level.

[swuehl]

Please note that QV is case sensitive.

In your above expression, you used [Customer satisfaction] vs [Customer Satisfaction].

Maybe just a copy & paste typo, maybe not.

[#4]

I'm using a text box so there are no dimensions(?).

[#5]

I simply changed the name since the original was in another language. So that should not be the problem.

[jonathandienst]

Edwin Vehmaanperä wrote:

avg({$<Year={2013}>}[Customer Satisfaction]) equals '-'

But then the expression will always evaluate as false. Any comparison with a NULL is always false. It seems likely that there is no data for 2013.

[PrashantSangle]

Hi,

try this

=if(avg({$<Year={"$(=Year(Today()))"}>}[Customer satisfaction])>=avg({$<Year={"$(=Year(AddYears(Today(),-1)))"}>}[Customer Satisfaction]),chr(9650),chr(9660))

if you need selected year then repalce today() with max(Year)

Regards

[tresesco]

Could you share a sample qvw that demonstrates the issue?

[prashantbaste]

Please check below expression & confirm -

=if(avg({<Year={2014}>}[Customer Satisfaction])>=avg({<Year={2015}>}[Customer Satisfaction]), chr(9650),chr(9660))

Its working fine at my end.

--

Regards,

Prashant P Baste

[#10 · Accepted solution]

Turns out the problem was that I had set Year as an integer in Document Settings Numbers-tab. After setting it backed to "Mixed" everything worked again. To me this should not make any difference. If I compare integers to integers then whats the problem? After all, outside of set analysis the comparison still worked which is why this was so hard to pin down. Only after loading the same data into a new document and seeing that it worked did I find the problem. Talk about frustrating 6 hours...

Thank you all for the help!