Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
If its a number then you dont require the quotes and if its a string then u require a quotes.
Regards,
Nirav Bhimani
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
HI,
Please see the following post for better understanding.
http://community.qlik.com/message/256279#256279
Regards,
Nirav Bhimani
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.
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
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.