I suggest extract Quatrer from a valid Date Field (no nulls) to make sure get correct data if you have nulls, specify a number like a default value ( if(isnull(MyField),1,MyField) ) and use it in your formula.
Something like this:
Ceil(Month( if(isnull(MyField),1,DateField) )/3) AS Quarter,
Q & '-' &if(isnull(QuarterField),1,MyField) as Quarter
I think you have null values or not a valid date values in the column Trade_Calendar_Quarter. I think it is better to create a new field instead of in frontend as calculated dimension
If(Len(Trade_Calendar_Quarter) > 0 AND IsNum(Trade_Calendar_Quarter) <> 0, 'Q' & Trade_Calendar_Quarter) AS Quarter
If you want the same in expression then try like this
=if(len(trim(Trade_Calendar_Quarter))>0 AND IsNum(Trade_Calendar_Quarter) <> 0, 'Q' & Trade_Calendar_Quarter)
Hope this helps you.
I'm going to call this answered as it does work. But I am still confused about why the expression
Q-' & Trade_Calendar_Quarter as Quarter
works in the load script but
Q-' & Trade_Calendar_Quarter
introduces the blank Q. And just using Trade_Calendar_Quarter in the list produces just 1 through 4.
There are NO nulls, invalid entries, etc in the table. This is coming from a standard calendar table with no other tables in the app. Sorry to be so frustrated, but there are times when QlikView is a total mystery to me.
I created a VERY simple qvw with this data:
load * Inline [
The list box with the column Quarter shows 1, 2, 3, 4
A list box with ='Q' & Quarter as the expression shows Q1, Q2, Q3, Q4, and Q
This is what I don't get. I can code around it, I can make it work, but why should I have to?
Dual is a very useful function! It is what I will use moving forward. I guess I'm just used to concatenating using a str() function. In other environments, it would throw an error if you forgot to type the numeric to a string. I guess in QlikView it allows it but gives strange results. I am going to utilize Dual more often.