topic Re: Distinct values in a list box in QlikView

Distinct values in a list box

Mon, 08 Feb 2016 19:57:42 GMT

Quick qn, I have same values being displayed multiple times in list box due to some concatenations I have in my model.

Is there a way to only display only the distinct values in the list box. I don't see the distinct function while trying to write expression in the list box.

The name of the field is [Report Month]

Thanks for all the help.

Regards,

Roy

Re: Distinct values in a list box

swuehl — Mon, 08 Feb 2016 21:19:50 GMT

How have you create this field?

I assume you have used a formatting function like Date() that keeps the underlying numeric value. So If you format a date as Year Month, you will get a list box entry per date, showing same Text representation for same Year Month.

Use e.g. MonthName(DateField) function instead, or

Date( Monthstart( DateField), 'MMM YYYY')

Re: Distinct values in a list box

harsh44_bhatia — Mon, 08 Feb 2016 21:21:59 GMT

Hello roy,

List boxes will always shown distinct values.

Here you are seeing multiple values because each of the month year values must be having different day.

for eg:

MONTH_YEAR: 1-Nov-2015, 4-Nov-2015, 7-Nov-2015

if you show this in list box as month year as per your attached screen shot you will definitely see 3 values, because each of them belongs to different day, no matter even if you cast them to month year in UI

i would suggest you to cast your date filed values as this in your SCRIPT:

monthName([date filed]) as Month_Year,

this will make dates to 1st day of the month, now when you call this in the list box it will surely show you one value

Re: Distinct values in a list box

Wed, 10 Feb 2016 19:49:09 GMT

Thanks Stefan apparently there was a problem in my model and I fixed it which ultimately resolved the issue in list box as well.