Selecting multiple values in Listbox

[#1]

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!

[sunny_talwar]

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

[swuehl]

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

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

[#4]

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.

[sunny_talwar · Accepted solution]

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

[swuehl]

Maybe like attached