Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Selecting multiple values in Listbox

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!

1 Solution

Accepted Solutions
Highlighted

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')

View solution in original post

5 Replies
Highlighted

May be a change in data model:

Capture.PNG

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')

Capture.PNG

Highlighted
MVP
MVP

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

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

Highlighted
Not applicable

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.

Highlighted

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')

View solution in original post

Highlighted
MVP
MVP

Maybe like attached