Why this calendar set analysis expression works and the other does not

[#1]

This may be a stupid question, but I was wondering in the set expressions below that came from a calendar that Miguel Angel Baeyens created, why one expression works and the other does not.  The calendar came from:  http://community.qlik.com/qlikviews/1075

This may help me with a concept that I am missing:

This is from the Dynamic chart:

The first expression and it works nice:

=Sum({< YTDOffset = {'$(=$(vYear1) - $(vYearMax))'} >} SalesAmount)                    // works from Miguel

=Sum({< YTDOffset = {'$(=($(vYear1) - $(vYearMax)))'} >} SalesAmount)                  // works similar to Miguel version, subtle difference in dollar expansion

=Sum({< YTDOffset = {$(=$(vYear1) - $(vYearMax))} >} SalesAmount)                     // does not work, only removed single quotes, appears to return:   -

=Sum({< YTDOffset = {$(=($(vYear1) - $(vYearMax)))} >} SalesAmount)                   // also does not work, only removed single quotes. appears to return:  -

Note, the only difference in these is that the two that work return '-3' for the set modifier while the two that do not work return -3.

The table appears to be numeric so I thought both would work.

I did a simple addtl pivot table with the following calcs and both return the same count of 364 for year 2007:

=IF(YTDOffset = '-3', COUNT(YTDOffset), 0)                                                            // match as string

=IF(YTDOffset = -3, COUNT(YTDOffset), 0)                                                            // match as number

Does anyone know what concept I am missing, why do I need the quotes?  Most set analysis have done have excluded the single quotes.

Thanks,

D

[nirav_bhimani]

Hi,

If its a number then you dont require the quotes and if its a  string then u require a quotes.

Regards,

Nirav Bhimani

[#3]

Understood and that makes sense.

However, how can I tell what type a field is? 

Why doesn't the other calc behave the same way, both appear to match the YTDOffset value?

=IF(YTDOffset = '-3', COUNT(YTDOffset), 0)                                                            // match as string

=IF(YTDOffset = -3, COUNT(YTDOffset), 0)                                                            // match as number

[nirav_bhimani]

HI,

Please see the following post for better understanding.

http://community.qlik.com/message/256279#256279

Regards,

Nirav Bhimani

[#5]

Hmm, nice post, but I don't believe that is the answer. 

I looked at the values, and the 4 values for this field are 0, -3, -2, -1

I do not have any spaces or any parens as the person in the article does.

I put this in a list box and it is justified as a number all the way on the right as well.

Not sure, but gives every appearance that it is a number, but fails in the set expression. 

[#6]

Note, there is no import here, Miguel calcs the field in question as below, the field that is being compared in the expressions in the first post of this dicussion is the YTDOffset.  See below, appears numeric from inception unless I am missing something:

LET vMaxDate = FieldValue('DateMax', 1);

LET vMinDate = FieldValue('DateMin', 1) -1; // Needed to control the minum date autogenerated in Sales table is included in the MasterCalendar

LET vYearMax = Year($(vMaxDate));

LET vYearMin = Year($(vMinDate));

FOR i = 0 TO $(vYearMax) - $(vYearMin)

          YTDTable:

          LOAD

                    CalendarYear,

                    CalendarDate AS YTDDate,

                    $(i) * -1 AS YTDOffset

          RESIDENT MasterCalendar

          WHERE InYearToDate(CalendarDate, Date($(vMaxDate)), $(i) * -1) = -1;

NEXT

[#7]

Note, the YTDOffset field is tagged by Qlikview as: $numeric, $integer

This also does not appear as a string value, not sure why single quotes needed in set.