RangeSum or Multiple Sums in Set Analysis

[#1]

Hi,

I want to sum 2 different amount values in set analysis. However, the 2 amount values are dependent on selections of 2 different fields available in list boxes. I prefer not to use any IF conditions and rather use set analysis. Is this possible?

NUM((RangeSum(If(GetSelectedCount(MonthCurrent) and [Category]='Credit',sum(SalesCurrent * -1)),If(GetSelectedCount(MonthProjected) and [Category]='Credit', Sum(SalesProjected * -1))),'$#,##0'))

[sunny_talwar]

Have not tested this, but may be try this:

RangeSum(

     Sum({<MonthCurrent = {"=GetSelectedCount(MonthCurrent) > 0"}, Category = {'Credit'}>} (SalesCurrent * -1)),

     Sum({<MonthProjected = {"=GetSelectedCount(MonthProjected) > 0"}, Category = {'Credit'}>} (SalesProjected * -1)))

[#3]

Thanks Sunny! This is coming up correct now. Can we show the sum even if no Months are selected ? i.e if no months are selected from MonthCurrent or MonthProjected, it should show the total sum of MonthCurrent and MonthProjected together.

[swuehl]

I think I know where you are coming from, but could you detail

- why you don't want to use the if() statement here?

Since it's not evaluated per record, there is no big performance impact here.

- How Category field is related to your fact / sales fields?

For checking the selection in month fields, I could imagine either a straight value assignment using an GetfieldSelections() dollar sign expansion in a field modifier (like  MonthCurrent = {$(=GetFieldSelections(MonthCurrent,',',12))} ) or shorter a field name field modifier ( MonthCurrent = MonthCurrentSelector ) using a data island or alternate state foryour selections (a solution which you don't like, if I remember correctly).

From a maintenance point of view, I don't think the set analysis expression will be easier to understand than the above posted expression (given that I think I know some things about your model).

[swuehl]

That should be default QV behaviour. Just remove the field modifier for months field or the if() statement that checks selections in months fields.

[Anonymous]

According to My sum({<Field name={'Value'}>}Sales)+ sum({<Field name={'Value'}>}Sales)

try it in this way