Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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')
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')
Or without changing your data model, use alternate state for projected selection
= num(sum({$<Period = {'Current'}>+Projected<Period={'Projected'}>} [Sales Amount]),'#,##0')
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.
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')
Maybe like attached