topic Re: Listbox filtered in QlikView

Listbox filtered

Wed, 17 May 2017 11:54:20 GMT

Hello,

I am new to QlikView and I need a listbox which only displays values which fulfill a condition.

The listbox should only display those suppliers of the field 'Supplier' which pass over a threshold.

The threshold should be a percentage (chosen with a scroll bar) of the sum of the total volume of a fiscal year.

I have the fields Supplier, Volume, Fiscal Year.

If tried this expression as listbox field but it is not working:

(Variable is the variable connected to the scroll bar to choose the percentage from 0% - 100%)

= if( aggr( Volume , Supplier ) >= Variable * Sum(Volume) , Supplier )

How can I solve this problem?

Best wishes

Martin

Re: Listbox filtered

pipuindia99 — Wed, 17 May 2017 12:30:11 GMT

Hi,

try this,

= if( aggr( Volume , Supplier ) >= $(Variable) * Sum(Volume) , Supplier )

if not working please post your sample qvw,

Re: Listbox filtered

Thu, 18 May 2017 07:06:53 GMT

Hello,

no it still does not work.

This is my loading script:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD.MM.YYYY';

SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='de-DE';

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

SET LongMonthNames='Januar;Februar;März;April;Mai;Juni;Juli;August;September;Oktober;November;Dezember';

SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag';

LOAD [Fiscal Year (FY)] as [Fiscal Year], [Supplier Number (IvS)] as Supplier,

EUR as Volume

FROM

(txt, utf8, embedded labels, delimiter is ';', msq, header is 1 lines);

With the percentage scroll-bar I want to choose the companies for example that make up 20% of the total Volume in Fiscal Year 2016.

This is the expression in the listbox:

This is configuration in the scroll-bar:

Thank you for helping me!

Re: Listbox filtered

Thu, 18 May 2017 07:47:07 GMT

I am not sure if the formula works at all. I want to filter the suppliers in the list box, so that only, for example, 20% of the companies with the biggest volume are displayed.

I hope you can help me to solve this problem.