16 Replies Latest reply: Jun 25, 2014 6:24 PM by Nilo Barreto Pontes

# Question on List box expression

Hi -

This is a simple question, with I hope a simple answer.  I have a Master Calendar with a Trade_Calendar_Quarter column.  It is 1, 2, 3, or 4 depending upon in which quarter the date falls.  I can create a list box with that column in it, and it shows 1, 2, 3, and 4, as expected.  I wanted it to look nice, so I made it an expression:

With that as the expression, it shows Q, Q1, Q2, Q3, and Q4.  The Q is grayed out, as it is not possible.

So the question is really two questions: why does that happen, and how can I not have the Q show up?

• ###### Re: Question on List box expression

You have hide excluded  in properties of that list box if you don't want to show Q .

It was not showed you just made an expression at front end .

• ###### Re: Question on List box expression

I'm afraid I've tried that, and it doesn't work.

• ###### Re: Question on List box expression

Hi,

Use this in your master calendar:

Ceil(Month(DateField)/3) AS Quarter,

Best regards.

• ###### Re: Question on List box expression

This would just give me 1, 2, 3, and 4.  I already have that.  It's the concatenation of 'Q' that is causing the problem.

• ###### Re: Question on List box expression

Blair, try the Dual function.

Best regards

• ###### Re: Question on List box expression

This does work, but it seems very strange that if there are no values in the calendar for that column other than 1, 2, 3, and 4 that it would be necessary.  Why does concatenating the 'Q' seem to create a 'new' value?

• ###### Re: Question on List box expression

Hi,

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,

or

Q & '-' &if(isnull(QuarterField),1,MyField) as Quarter

Best regards.

• ###### Re: Question on List box expression

The second expression works.  However, so does just this:

Why does this work in the load script but not in the expression on the list box?  Why does the list box seem to introduce a null/empty value that isn't really there?

• ###### Re: Question on List box expression

Hi,

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

*,

FROM DataSource;

If you want the same in expression then try like this

Hope this helps you.

Regards,

Jagan.

• ###### Re: Question on List box expression

I'm going to call this answered as it does work.  But I am still confused about why the expression

works in the load script but

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.

• ###### Re: Question on List box expression

I created a VERY simple qvw with this data:

Calendar:

Date, Quarter

1/1/14, 1

4/1/14, 2

7/1/14, 3

10/1/14, 4

];

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?

• ###### Re: Question on List box expression

Blair,

use the Dual function on the list box expression, i.e.:

=Dual('Q' & Quarter,Quarter)

Hope it helps you.

Best regards

• ###### Re: Question on List box expression

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.

• ###### Re: Question on List box expression

Dual is very useful for many proposes, I use it many times to add fancy labels on charts.