topic Selecting multiple values in Listbox in QlikView

Selecting multiple values in Listbox

Mon, 01 Feb 2016 18:17:00 GMT

Hi,

I have current and projected sales data. I want to calculate the amount for current sales as well as for projected sales. However, I am not able to make accurate selections using 2 separate list boxes or combination of 1 list box. For example: I want to calculate sales amount for current Jan to June and for projected July to August. On selecting Projected July and August, Current July and August also gets selected which should not. Attached sample. Any idea?

Thanks!

Re: Selecting multiple values in Listbox

sunny_talwar — Mon, 01 Feb 2016 19:15:40 GMT

May be a change in data model:

Script:

Table:

LOAD Date#(Month, 'MMMM') as Month,

[Sales Amount],

Period;

LOAD * INLINE [

Month, Sales Amount, Period

Jan, 6549, Current

Feb, 9897, Current

March, 7869, Current

April, 8765, Current

May, 8767, Current

June, 9875, Current

July, 6565, Current

August, 8889, Current

September, 6765, Current

October, 6767, Current

November, 8876, Current

December, 9865, Current

July, 9089, Projected

August, 6785, Projected

September, 9765, Projected

October, 8797, Projected

November, 8976, Projected

December, 8754, Projected

];

Current:

LOAD Month as Current_Month,

[Sales Amount] as Cur_Sales_Amount

Resident Table

Where Period = 'Current';

Projected:

LOAD Month as Projected_Month,

[Sales Amount] as Proj_Sales_Amount

Resident Table

Where Period = 'Projected';

DROP Table Table;

Front end output:

Expression:

=Num(RangeSum(Sum(Cur_Sales_Amount), Sum(Proj_Sales_Amount)), '#,##0')

Re: Selecting multiple values in Listbox

swuehl — Mon, 01 Feb 2016 19:27:59 GMT

Or without changing your data model, use alternate state for projected selection

= num(sum({$<Period = {'Current'}>+Projected<Period={'Projected'}>} [Sales Amount]),'#,##0')

Re: Selecting multiple values in Listbox

Mon, 01 Feb 2016 19:49:02 GMT

Thanks Swuehl! This is helpful. However, if you select just Projected month/s and not current month/s, the current months are selected by default. For example: If you clear all selections and just select Projected Jan and Feb or Projected July and August, all the months from current are selected. See attached.

Re: Selecting multiple values in Listbox

sunny_talwar — Mon, 01 Feb 2016 20:08:16 GMT

Give this a try:

=Num(Sum({$<Period = {$(=If(GetSelectedCount(Month, True(), '$') = 0, 'xyz', 'Current'))}>+

Projected<Period={$(=If(GetSelectedCount(Month, True(), 'Projected') = 0, 'xyz', 'Projected'))}>} [Sales Amount]),'#,##0')

Re: Selecting multiple values in Listbox

swuehl — Mon, 01 Feb 2016 20:30:31 GMT

Maybe like attached